﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.Linq.SqlClient;
using System.Web.Configuration;

namespace TripVoyage
{
    public class QPlan : IQPlan
    {
        CoastripDataContext DC = new CoastripDataContext();

        /*************************************************************************************/
        /********                                                                   **********/
        /*************************************************************************************/
        //帐户
        public string Account()
        {
            return Comn.Visitor();
        }

        //港口局
        public bool InGovern()
        {
            try
            {
                return Comn.IsCredit("Govern");
            }
            catch (SystemException ex)
            {
                throw new FaultException(ex.Message);
            }
        }
        //总调
        public bool InCenter()
        {
            try
            {
                return Comn.IsCredit("Center");
            }
            catch (SystemException ex)
            {
                throw new FaultException(ex.Message);
            }
        }
        //船务
        public short InShip(bool ship)
        {
            try
            {
                if (Comn.IsCredit("YClerk"))
                {
                    string usr = Comn.Visitor();
                    var Res = DC.Yclerk.Where(k => k.Acnt == usr && k.Senior == ship);
                    if (Res.Count() > 0)
                    {
                        if (DC.Yacht.Where(y => y.Ymx == Res.First().Ymx).First().Ready)
                            return Res.First().Ymx;
                        else
                            return (short)0;
                    }
                }
            }
            catch (SystemException ex)
            {
                throw new FaultException(ex.Message);
            }
            return (short)0;
        }

        //分调、检票
        public short InBranch(bool branch)
        {
            try
            {
                if (Comn.IsCredit("WClerk"))
                {
                    string usr = Comn.Visitor();
                    var Res = DC.Wclerk.Where(k => k.Acnt == usr && k.Senior == branch);
                    if (Res.Count() > 0)
                    {
                        if (DC.Wharf.Where(y => y.Wmx == Res.First().Wmx).First().Ready)
                            return Res.First().Wmx;
                        else
                            return (short)0;
                    }
                }
            }
            catch (SystemException ex)
            {
                throw new FaultException(ex.Message);
            }
            return (short)0;
        }
        //票证
        public bool InIssuer()
        {
            try
            {
                return Comn.IsCredit("Issuer");
            }
            catch (SystemException ex)
            {
                throw new FaultException(ex.Message);
            }
        }
        //售票、订票
        public short InSale(bool book)
        {
            try
            {
                if (Comn.IsCredit("AClerk"))
                {
                    string usr = Comn.Visitor();
                    var Res = DC.Aclerk.Where(k => k.Acnt == usr && k.Senior == book);
                    
                    if (Res.Count() > 0)
                    {
                        if (DC.Agent.Where(y => y.Amx == Res.First().Amx).First().Ready)
                            return Res.First().Amx;
                        else
                            return (short)0;
                    }
                }
            }
            catch (SystemException ex)
            {
                throw new FaultException(ex.Message);
            }
            return (short)0;
        }
        
        /*************************************************************************************/
        /********                                                                   **********/
        /*************************************************************************************/
        private float To10(float fNum)
        {
            float fResult = 0;
            string[] sNum = fNum.ToString().Split('.');
            string sTen = "";
            //if语句的else前面部分是判断小数的，注释后则不判断小数
            //if (sNum.Length == 2)
            //{
            //    if (int.Parse(sNum[1]) > 0 || int.Parse(sNum[0].Substring(sNum[0].Length - 1, 1)) > 0)
            //    {
            //        if (sNum[0].Length > 1)
            //        {
            //            sTen = sNum[0].Substring(0, sNum[0].Length - 1);
            //            if (sTen == "9")
            //                fResult = float.Parse((int.Parse(sTen) + 1).ToString().PadRight(sNum[0].Length + 1, '0'));
            //            else
            //                fResult = float.Parse((int.Parse(sTen) + 1).ToString().PadRight(sNum[0].Length, '0'));
            //        }
            //        else
            //            fResult = 10f;
            //    }
            //    else
            //        fResult = float.Parse(sNum[0]);
            //}
            //else
            //{
            if (int.Parse(sNum[0].Substring(sNum[0].Length - 1, 1)) > 0)
            {
                if (sNum[0].Length > 1)
                {
                    sTen = sNum[0].Substring(0, sNum[0].Length - 1);
                    if (sTen == "9")
                        fResult = float.Parse((int.Parse(sTen) + 1).ToString().PadRight(sNum[0].Length + 1, '0'));
                    else
                        fResult = float.Parse((int.Parse(sTen) + 1).ToString().PadRight(sNum[0].Length, '0'));
                }
                else
                    fResult = 10f;
            }
            else
                fResult = float.Parse(sNum[0]);
            //}
            return fResult;
        }

        private ulong AddSectQuse(int Key, bool IsVoyage)
        {
            ulong uResult = 0;
            string[] arrayTotem = IsVoyage ? GetVoyageTotem(Key).Split('|') : GetTotem(short.Parse(Key.ToString())).Split('|');
            uResult = ulong.Parse(Convert.ToString(int.Parse(arrayTotem[0]), 2));
            return uResult;
        }

        private string GetTotem(short Fmx)
        {
            short sOmx = DC.Finish.Where(y => y.Fmx == Fmx).First().Omx;
            short sFromW = DC.Origin.Where(y => y.Omx == sOmx).First().Wmx;
            short sDestW = DC.Finish.Where(y => y.Fmx == Fmx).First().Wmx;
            Dictionary<int, short> dWharf = GetWharfDic(Fmx);
            string sResult = MakeFlag(dWharf, sFromW, sDestW);
            return sResult;
        }

        private string GetVoyageTotem(int Ynx)
        {
            short sFromW = (from y in DC.Wharfiny
                            join f in DC.Wharfrom on y.Fnx equals f.Fnx
                            where y.Ynx == Ynx
                            select new { Wmx = f.Wmx }).First().Wmx;
            short sDestW = DC.Wharfiny.Where(y => y.Ynx == Ynx).First().Wmx;
            Dictionary<int, short> dWharf = GetVoyageWharfDic(Ynx);
            string sResult = MakeFlag(dWharf,sFromW,sDestW);
            return sResult;
        }

        private string MakeFlag(Dictionary<int, short> dic,short from,short dest)
        {
            //产生Flag字符串
            string sResult = "";
            int iFlag = 0;
            Char[] arrayFlag = new Char[dic.Count() - 1];
            char cFlag = '0';
            short sWharf = 0;
            for (int i = 0; i < dic.Count() - 1; i++)
            {
                dic.TryGetValue(i, out sWharf);
                if (sWharf == from) cFlag = '0';
                if (sWharf == dest) cFlag = '1';
                arrayFlag[i] = cFlag;
            }
            iFlag = Convert.ToInt32(new String(arrayFlag), 2);
            sResult = iFlag.ToString() + "|" + (dic.Count() - 1).ToString();
            return sResult;
        }

        private int MakeFlagNum(Dictionary<int, short> dic, short from, short dest)
        {
            //产生Flag字符串
            int iResult = 0;
            Char[] arrayFlag = new Char[dic.Count() - 1];
            char cFlag = '0';
            short sWharf = 0;
            for (int i = 0; i < dic.Count() - 1; i++)
            {
                dic.TryGetValue(i, out sWharf);
                if (sWharf == from) cFlag = '0';
                if (sWharf == dest) cFlag = '1';
                arrayFlag[i] = cFlag;
            }
            iResult = Convert.ToInt32(new String(arrayFlag), 2);
            return iResult;
        }

        public Dictionary<int, short> GetVoyageWharfDicByVnx(int Vnx)
        {
            var Res1 = from y in DC.Wharfiny
                       join f in DC.Wharfrom on y.Fnx equals f.Fnx
                       where (f.Vnx == Vnx)
                       select new
                       {
                           FromW = f.Wmx
                       };
            //按数量排序，靠后的码头在前，靠前的码头在后
            var Res2 = from r1 in Res1
                       group r1 by new
                       {
                           r1.FromW
                       }
                           into zz
                           orderby zz.Count()
                           select new
                           {
                               Key = zz.Key,
                               CountW = zz.Count()
                           };
            //按数量倒序排列，第一行即是终点码头
            var Res3 = from y in DC.Wharfiny
                       join f in DC.Wharfrom on y.Fnx equals f.Fnx
                       where (f.Vnx == Vnx)
                       select new
                       {
                           DestW = y.Wmx
                       };
            var Res4 = from r3 in Res3
                       group r3 by new
                       {
                           r3.DestW
                       }
                           into zzz
                           orderby zzz.Count() descending
                           select new
                           {
                               Key = zzz.Key,
                               CountW = zzz.Count()
                           };

            Dictionary<int, short> dWharf = new Dictionary<int, short>();
            //先添加终点
            dWharf.Add(0, Res4.First().Key.DestW);
            foreach (var c in Res2)
            {
                dWharf.Add(c.CountW, c.Key.FromW);
            }
            return dWharf;
        }

        private Dictionary<int, short> GetVoyageWharfDic(int Ynx)
        {
            //产生航线码头的字典
            int iVnx = (from y in DC.Wharfiny
                        join f in DC.Wharfrom on y.Fnx equals f.Fnx
                        where y.Ynx == Ynx
                        select new { Vnx = f.Vnx }).First().Vnx;

            Dictionary<int, short> dWharf = GetVoyageWharfDicByVnx(iVnx);
            return dWharf;
        }

        public Dictionary<int, short> GetWharfDicByCmx(short Cmx)
        {
            var Res1 = from f in DC.Finish
                       join o in DC.Origin on f.Omx equals o.Omx
                       where (o.Cmx == Cmx)
                       select new
                       {
                           FromW = o.Wmx
                       };
            //按数量排序，靠后的码头在前，靠前的码头在后
            var Res2 = from r1 in Res1
                       group r1 by new
                       {
                           r1.FromW
                       }
                           into zz
                           orderby zz.Count()
                           select new
                           {
                               Key = zz.Key,
                               CountW = zz.Count()
                           };
            //按数量倒序排列，第一行即是终点码头
            var Res3 = from f in DC.Finish
                       join o in DC.Origin on f.Omx equals o.Omx
                       where (o.Cmx == Cmx)
                       select new
                       {
                           DestW = f.Wmx
                       };
            var Res4 = from r3 in Res3
                       group r3 by new
                       {
                           r3.DestW
                       }
                           into zzz
                           orderby zzz.Count() descending
                           select new
                           {
                               Key = zzz.Key,
                               CountW = zzz.Count()
                           };

            Dictionary<int, short> dWharf = new Dictionary<int, short>();
            //先添加终点
            dWharf.Add(0, Res4.First().Key.DestW);
            foreach (var c in Res2)
            {
                dWharf.Add(c.CountW, c.Key.FromW);
            }
            return dWharf;
        }

        private Dictionary<int, short> GetWharfDic(short Fmx)
        {
            //产生航线码头的字典
            short sOmx = DC.Finish.Where(y => y.Fmx == Fmx).First().Omx;
            short sCmx = DC.Origin.Where(y => y.Omx == sOmx).First().Cmx;

            Dictionary<int, short> dWharf = GetWharfDicByCmx(sCmx);
            return dWharf;
        }

        private int GetSectCount(int Vnx)
        {
            int iResult = 0;
            var SectCount = from f in DC.Wharfiny
                            join o in DC.Wharfrom on f.Fnx equals o.Fnx
                            where o.Vnx == Vnx
                            select new
                            {
                                Wmx = f.Wmx
                            };
            iResult = SectCount.Distinct().Count();
            return iResult;
        }
        /*************************************************************************************/
        /********                                                                   **********/
        /*************************************************************************************/

        public string CheckLabelOut(int Pnx)
        {
            string sResult = "";
            if (DC.Sellout.Where(x => x.Pnx == Pnx).Count() > 0) sResult = "Sellout";
            if (DC.Luggout.Where(x => x.Pnx == Pnx).Count() > 0) sResult = "Luggout";
            if (DC.Rollout.Where(x => x.Pnx == Pnx).Count() > 0) sResult = "Rollout";
            return sResult;
        }

        /*************************************************************************************/
        /********                                                                   **********/
        /*************************************************************************************/
        public Dictionary<short, string> DicAgent()
        {
            Dictionary<short, string> dAgent = new Dictionary<short, string>();
            foreach (var c in DC.Agent)
            {
                dAgent.Add(c.Amx, c.Nym);
            }
            return dAgent;
        }
        public Dictionary<string, string> DicAclerk(short Amx)
        {
            Dictionary<string, string> dAclerk = new Dictionary<string, string>();
            foreach (var c in DC.Aclerk.Where(y=> y.Amx == Amx))
            {
                dAclerk.Add(c.Acnt, Comn.AClerkDisplay(c.Acnt));
            }
            return dAclerk;
        }
        public Dictionary<string, string> DicYFirm()
        {
            Dictionary<string, string> dYFirm = new Dictionary<string, string>();
            var Result = (from y in DC.Yacht
                          select new
                          {
                              Firm = y.Firm
                          }).Distinct();

            dYFirm.Add("全部", "全部");
            foreach (var c in Result)
            {
                dYFirm.Add(c.Firm, c.Firm);
            }
            return dYFirm;
        }
        public Dictionary<string, string> DicWFirm()
        {
            Dictionary<string, string> dWFirm = new Dictionary<string, string>();
            var Result = (from w in DC.Wharf
                          select new
                          {
                              Firm = w.Firm
                          }).Distinct();

            dWFirm.Add("全部", "全部");
            foreach (var c in Result)
            {
                dWFirm.Add(c.Firm, c.Firm);
            }
            return dWFirm;
        }
        public Dictionary<string, string> DicAFirm()
        {
            Dictionary<string, string> dAFirm = new Dictionary<string, string>();
            var Result = (from a in DC.Agent
                          select new
                          {
                              Firm = a.Firm
                          }).Distinct();

            dAFirm.Add("全部", "全部");
            foreach (var c in Result)
            {
                dAFirm.Add(c.Firm, c.Firm);
            }
            return dAFirm;
        }
        public Dictionary<short, string> DicOutYC(short Ymx, short Cmx)
        {
            Dictionary<short, string> dOutYC = new Dictionary<short, string>();
            var Result = (from b in DC.Lbunk
                         join i in DC.Cabin on b.Cmx equals i.Cmx
                         join g in DC.Grade on i.Gmx equals g.Gmx
                         join y in DC.Yacht on g.Ymx equals y.Ymx
                         join c in DC.Course on b.Lmx equals c.Cmx
                         where (c.Cmx == Cmx || Cmx == 0)
                         && (y.Ymx == Ymx || Ymx == 0)
                         orderby c.Cmx, y.Ymx, g.Gmx, i.Cmx, b.Bnum
                         select new
                         {
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             CName = c.Nym,
                             YName = y.Nym
                         }).Distinct();

            if (Ymx > 0)
            {
                foreach (var c in Result)
                {
                    if (!dOutYC.ContainsKey(c.Cmx)) dOutYC.Add(c.Cmx, c.CName);
                }
            }
            else
            {
                foreach (var c in Result)
                {
                    if (!dOutYC.ContainsKey(c.Ymx)) dOutYC.Add(c.Ymx, c.YName);
                }
            }
            return dOutYC;
        }

        /*************************************************************************************/
        /********                                                                   **********/
        /*************************************************************************************/
        public List<AgentClass> GetAgent()
        {
            List<AgentClass> ResultList = new List<AgentClass>();
            foreach (var c in DC.Agent)
            {
                AgentClass ccc = new AgentClass
                {
                    Amx = c.Amx,
                    Firm = c.Firm,
                    Nym = c.Nym,
                    Ready = c.Ready,
                    Addr = c.Addr
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<QuseClass> GetQuse(short Amx)
        {
            List<QuseClass> ResultList = new List<QuseClass>();
            var Result = from u in DC.Quse
                         join a in DC.Agent on u.Amx equals a.Amx
                         where a.Amx == Amx
                         select new
                         {
                             AName = a.Nym,
                             Nym = u.Nym,
                             Amx = a.Amx,
                             Umx = u.Umx
                         };
            foreach (var c in Result)
            {
                QuseClass ccc = new QuseClass
                {
                    AName = c.AName,
                    Nym = c.Nym,
                    Amx = c.Amx,
                    Umx = c.Umx
                };
                ResultList.Add(ccc);
            }
            return ResultList; 
        }

        public List<WharfClass> GetWharf()
        {
            List<WharfClass> ResultList = new List<WharfClass>();
            foreach (var c in DC.Wharf)
            {
                WharfClass ccc = new WharfClass
                {
                    Wmx = c.Wmx,
                    Firm = c.Firm,
                    Nym = c.Nym,
                    Ready = c.Ready,
                    City = c.City,
                    Gate = c.Gate,
                    Afee = c.Afee,
                    Wfee = c.Wfee
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<YachtClass> GetYacht()
        {
            List<YachtClass> ResultList = new List<YachtClass>();
            foreach (var c in DC.Yacht)
            {
                YachtClass ccc = new YachtClass
                {
                    Ymx = c.Ymx,
                    Firm = c.Firm,
                    Nym = c.Nym,
                    Ready = c.Ready,
                    LuggFee = c.LuggFee
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<GradeClass> GetGrade(short Ymx)
        {
            List<GradeClass> ResultList = new List<GradeClass>();
            foreach (var c in DC.Grade.Where(y => y.Ymx == Ymx))
            {
                GradeClass ccc = new GradeClass
                {
                    Gmx = c.Gmx,
                    Nym = c.Nym,
                    Discnt = c.Discnt,
                    Rate = c.Rate,
                    Ymx = c.Ymx,
                    BunkFee = c.BunkFee
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }
       
        public List<CabinClass> GetCabin(short Ymx)
        {
            List<CabinClass> ResultList = new List<CabinClass>();
            var Result = from i in DC.Cabin
                         join g in DC.Grade on i.Gmx equals g.Gmx
                         join y in DC.Yacht on g.Ymx equals y.Ymx
                         where y.Ymx == Ymx
                         select new
                         {
                             GName = g.Nym,
                             Cmx = i.Cmx,
                             Nym = i.Nym,
                             First = i.First,
                             Last = i.Last
                         };
            foreach (var c in Result)
            {
                CabinClass ccc = new CabinClass
                {
                    GName = c.GName,
                    Cmx = c.Cmx,
                    Nym = c.Nym,
                    First = c.First,
                    Last = c.Last
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<AclerkClass> GetAclerk(short Amx)
        {
            List<AclerkClass> ResultList = new List<AclerkClass>();
            foreach (var c in (Amx == 0 ? DC.Aclerk : DC.Aclerk.Where(y => y.Amx == Amx)))
            {
                AclerkClass ccc = new AclerkClass
                {
                    Amx = c.Amx,
                    Acnt = c.Acnt,
                    Display = Comn.AClerkDisplay(c.Acnt),
                    Senior = c.Senior
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<WclerkClass> GetWclerk(short Wmx)
        {
            List<WclerkClass> ResultList = new List<WclerkClass>();
            foreach (var c in (Wmx == 0 ? DC.Wclerk : DC.Wclerk.Where(y => y.Wmx == Wmx)))
            {
                WclerkClass ccc = new WclerkClass
                {
                    Wmx = c.Wmx,
                    Acnt = c.Acnt,
                    Display = Comn.Groupersons(WebConfigurationManager.AppSettings["WClerk"])[c.Acnt],
                    Senior = c.Senior
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<CourseClass> GetCourse()
        {
            List<CourseClass> ResultList = new List<CourseClass>();
            foreach (var c in DC.Course)
            {
                CourseClass ccc = new CourseClass
                {
                    Cmx   = c.Cmx,
                    Nym = c.Nym,
                    FullCourse = GetFullCourseByCmx(c.Cmx),
                    Toggle = c.Toggle.ToShortDateString(),
                    To2 =c.To2 ? "1# -> 2#" : "2# -> 1#" 
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<MotypeClass> GetMotype(short Ymx)
        {
            List<MotypeClass> ResultList = new List<MotypeClass>();
            foreach (var c in DC.Motype.Where(y => y.Ymx == Ymx))
            {
                MotypeClass ccc = new MotypeClass
                {
                    Nym = c.Tnym,
                    Tmx = c.Tmx,
                    Ymx = c.Ymx,
                    RollFee = c.RollFee
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<FinishClass> GetFinish(short Cmx)
        {
            List<FinishClass> ResultList = new List<FinishClass>();
            var Result  = from f in DC.Finish
                          join wf in DC.Wharf on f.Wmx equals wf.Wmx
                          join o in DC.Origin on f.Omx equals o.Omx
                          join wo in DC.Wharf on o.Wmx equals wo.Wmx
                          join c in DC.Course on o.Cmx equals c.Cmx
                          where c.Cmx == Cmx
                          select new
                          {
                              Fmx = f.Fmx,
                              Cmx = c.Cmx,
                              FromW = wo.Nym,
                              DestW = wf.Nym,
                              Fare1 = f.Fare1,
                              Fare2 = f.Fare2
                          };


            foreach (var c in Result)
            {
                FinishClass ccc = new FinishClass
                {
                    Fmx = c.Fmx,
                    Cmx = c.Cmx,
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Fare1 = c.Fare1,
                    Fare2 = c.Fare2,
                    Totem = GetTotem(c.Fmx)
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<BustopClass> GetBustop()
        {
            List<BustopClass> ResultList = new List<BustopClass>();
            foreach (var c in DC.Bustop)
            {
                BustopClass ccc = new BustopClass
                {
                    Nym = c.Bnym,
                    Bmx = c.Bmx,
                    Ready = c.Ready,
                    Afee = c.Afee,
                    Firm = c.Firm
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<StopriceClass> GetStoprice(short Wmx, short Ymx, short Bmx)
        {
            List<StopriceClass> ResultList = new List<StopriceClass>();
            var Result  = from p in DC.Stoprice
                          join y in DC.Yacht on p.Ymx equals y.Ymx
                          join w in DC.Wharf on p.Wmx equals w.Wmx
                          join b in DC.Bustop on p.Bmx equals b.Bmx
                          where (w.Wmx == Wmx || Wmx == 0)
                          && (y.Ymx == Ymx || Ymx == 0)
                          && (b.Bmx == Bmx || Bmx == 0)
                          select new
                          {
                              Wmx = w.Wmx,
                              Ymx = y.Ymx,
                              Bmx = b.Bmx,
                              WName = w.Nym,
                              YName = y.Nym,
                              BName = b.Bnym,
                              Fare1 = p.Fare1,
                              Fare2 = p.Fare2,
                              Toggle = p.Toggle,
                              To2 = p.To2,
                              Stopay = p.Stopay
                          };

            foreach (var c in Result)
            {
                StopriceClass ccc = new StopriceClass
                {
                    Wmx = c.Wmx,
                    Ymx = c.Ymx,
                    Bmx = c.Bmx,
                    WName = c.WName,
                    YName = c.YName,
                    BName = c.BName,
                    Fare1 = c.Fare1,
                    Fare2 = c.Fare2,
                    Toggle = c.Toggle.ToShortDateString(),
                    To2 = c.To2 ? "1# -> 2#" : "2# -> 1#",
                    Stopay = c.Stopay
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<LinerClass> GetLiner(short Cmx, short Ymx)
        {
            List<LinerClass> ResultList = new List<LinerClass>();
            var Result = from l in DC.Liner
                         join c in DC.Course on l.Cmx equals c.Cmx
                         join y in DC.Yacht on l.Ymx equals y.Ymx
                         where (c.Cmx == Cmx || Cmx == 0)
                         && (y.Ymx == Ymx || Ymx == 0)
                         select new
                         {
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             CName = c.Nym,
                             YName = y.Nym,
                             BookFee = l.BookFee,
                             Preact = l.Preact,
                             Topn = l.Topn
                         };

            foreach (var c in Result)
            {
                LinerClass ccc = new LinerClass
                {
                    Cmx = c.Cmx,
                    Ymx = c.Ymx,
                    CName = c.CName,
                    YName = c.YName,
                    BookFee = c.BookFee,
                    Preact = c.Preact + "天",
                    Topn = (c.Topn / 60).ToString() + ":" + (c.Topn % 60).ToString().PadLeft(2,'0'),
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }
         
        public List<ShiproxyClass> GetShiproxy(short Cmx, short Ymx, short Amx)
        {
            List<ShiproxyClass> ResultList = new List<ShiproxyClass>();
            var Result  = from x in DC.Shiproxy
                          join o in DC.Origin on x.Omx equals o.Omx
                          join c in DC.Course on o.Cmx equals c.Cmx
                          join a in DC.Agent on x.Amx equals a.Amx
                          join y in DC.Yacht on x.Ymx equals y.Ymx
                          join w in DC.Wharf on o.Wmx equals w.Wmx
                          where (c.Cmx == Cmx || Cmx == 0)
                          && (a.Amx == Amx || Amx == 0)
                          && (y.Ymx == Ymx || Ymx == 0)
                          select new
                          {
                              Cmx = c.Cmx,
                              Ymx = y.Ymx,
                              Amx = a.Amx,
                              CName = c.Nym,
                              OName = w.Nym,
                              YName = y.Nym,
                              AName = a.Nym,
                              Credit = x.Credit,
                              Tdwn = x.Tdwn,
                              Qdwn = x.Qdwn,
                              Rdwn = x.Rdwn
                          };

            foreach (var c in Result)
            {
                ShiproxyClass ccc = new ShiproxyClass
                {
                    Cmx = c.Cmx,
                    Ymx = c.Ymx,
                    Amx = c.Amx,
                    CName = c.CName,
                    OName = c.OName,
                    YName = c.YName,
                    AName = c.AName,
                    Tcar = (c.Credit & 4) == 0 ? false : true,
                    Tbag = (c.Credit & 2) == 0 ? false : true,
                    Retn = (c.Credit & 1) == 0 ? false : true,
                    Tdwn = c.Tdwn + "分钟",
                    Qdwn = c.Qdwn + "小时",
                    Rdwn = c.Rdwn + "分钟"
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<ShipageClass> GetShipage(short Ymx, short Amx)
        {
            List<ShipageClass> ResultList = new List<ShipageClass>();
            var Result = DC.Shipage.Where(y=>(y.Amx == Amx || Amx ==0) &&(y.Ymx == Ymx || Ymx ==0));

            foreach (var c in Result)
            {
                ShipageClass ccc = new ShipageClass
                {
                    Ymx = c.Ymx,
                    Amx = c.Amx,
                    YName = DC.Yacht.ToDictionary(y => y.Ymx)[c.Ymx].Nym,
                    AName = DC.Agent.ToDictionary(y => y.Amx)[c.Amx].Nym,
                    SellRate = c.SellRate,
                    RetnRate = c.RetnRate
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<ShiportClass> GetShiport(short Ymx, short Wmx)
        {
            List<ShiportClass> ResultList = new List<ShiportClass>();
            var Result = DC.Shiport.Where(y => (y.Wmx == Wmx || Wmx == 0) && (y.Ymx == Ymx || Ymx == 0));

            foreach (var c in Result)
            {
                ShiportClass ccc = new ShiportClass
                {
                    Ymx = c.Ymx,
                    Wmx = c.Wmx,
                    YName = DC.Yacht.ToDictionary(y => y.Ymx)[c.Ymx].Nym,
                    WName = DC.Wharf.ToDictionary(y => y.Wmx)[c.Wmx].Nym,
                    Stopay = c.Stopay,
                    Busfee = c.Busfee,
                    DockRate = c.DockRate
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }


        public List<VallotClass> GetVallot(int Vnx)
        {
            List<VallotClass> ResultList = new List<VallotClass>();
            var Result = from l in DC.Vallot
                         join u in DC.Quse on l.Umx equals u.Umx
                         join a in DC.Agent on u.Amx equals a.Amx
                         join p in DC.Gfare on l.Pnx equals p.Pnx
                         join g in DC.Grade on p.Gmx equals g.Gmx
                         join wy in DC.Wharfiny on p.Ynx equals wy.Ynx
                         join wwy in DC.Wharf on wy.Wmx equals wwy.Wmx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         join v in DC.Voyage on wf.Vnx equals v.Vnx
                         join ln in DC.Liner on v.Lmx equals ln.Lmx
                         join yh in DC.Yacht on ln.Ymx equals yh.Ymx
                         join cs in DC.Course on ln.Cmx equals cs.Cmx
                         where (v.Vnx == Vnx || Vnx == 0)
                         select new
                         {
                             Cmx = cs.Cmx,
                             CName = cs.Nym,
                             Ymx = yh.Ymx,
                             YName = yh.Nym,
                             GName = g.Nym,
                             UName = a.Nym + u.Nym,
                             First = l.First,
                             Last = l.Last,
                             Flag = p.Flag,
                             FromW = wwf.Nym,
                             DestW = wwy.Nym
                         };

            int iCount = GetSectCount(Vnx);

            foreach (var c in Result)
            {
                VallotClass ccc = new VallotClass
                {
                    Cmx = c.Cmx,
                    CName = c.CName,
                    Ymx = c.Ymx,
                    YName = c.YName,
                    GName = c.GName,
                    UName = c.UName,
                    First = c.First,
                    Last = c.Last,
                    QuseCount = c.Last - c.First + 1,
                    Flag = c.Flag.ToString() + "|" + iCount.ToString(),
                    FromW = c.FromW,
                    DestW = c.DestW
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<CBMapClass> GetCBMap(int Vnx)
        {
            int iCount = GetSectCount(Vnx);
            ulong uSectQuse = ulong.Parse(iCount.ToString() + "000000000000000000");

            List<CBMapClass> ResultList = new List<CBMapClass>();
            var Result = from b in DC.Vbunk
                         join i in DC.Cabin on b.Cmx equals i.Cmx
                         join g in DC.Grade on i.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join l in DC.Liner on v.Lmx equals l.Lmx
                         join y in DC.Yacht on l.Ymx equals y.Ymx
                         join c in DC.Course on l.Cmx equals c.Cmx
                         where (v.Vnx == Vnx)
                         orderby g.Gmx, c.Cmx, b.Bnum
                         select new
                         {
                             Tnx = b.Tnx,
                             Gmx = g.Gmx,
                             GName = g.Nym,
                             CName = i.Nym,
                             Bnum = b.Bnum,
                             Mask = b.Mask,
                             Ready = b.Ready
                         };


            foreach (var c in Result)
            {
                CBMapClass ccc = new CBMapClass
                {
                    Tnx = c.Tnx,
                    Gmx = c.Gmx,
                    GName = c.GName,
                    CName = c.CName,
                    Bnum = c.Bnum,
                    Mask = c.Mask.ToString() + "|" + iCount.ToString(),
                    Ready = c.Ready,
                    SectQuse = uSectQuse,
                    AgentQuse = ""
                };
                ResultList.Add(ccc);
            }

            var ResVlt = from l in DC.Vallot
                         join u in DC.Quse on l.Umx equals u.Umx
                         join a in DC.Agent on u.Amx equals a.Amx
                         join p in DC.Gfare on l.Pnx equals p.Pnx
                         join g in DC.Grade on p.Gmx equals g.Gmx
                         join wy in DC.Wharfiny on p.Ynx equals wy.Ynx
                         join wwy in DC.Wharf on wy.Wmx equals wwy.Wmx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         join v in DC.Voyage on wf.Vnx equals v.Vnx
                         join ln in DC.Liner on v.Lmx equals ln.Lmx
                         join yh in DC.Yacht on ln.Ymx equals yh.Ymx
                         join cs in DC.Course on ln.Cmx equals cs.Cmx
                         where (v.Vnx == Vnx)
                         select new
                         {
                             vFirst = l.First,
                             vLast = l.Last,
                             UName = a.Nym + u.Nym,
                             Gmx = g.Gmx,
                             Ynx = wy.Ynx
                         };

            foreach (var c in ResVlt)
            {
                ulong uAddSectQuse = AddSectQuse(c.Ynx, true);
                for (short x = c.vFirst; x <= c.vLast; x++)
                {
                    var ResBunk = ResultList.Where(y => y.Gmx == c.Gmx && y.Bnum == x);
                    //每个等级的留额预设只指定起始终止号，没有对等级铺位进行判断，这里需判断是否有此铺位
                    if (ResBunk.Count() > 0)
                    {
                        ResBunk.First().SectQuse += uAddSectQuse;
                        ResBunk.First().AgentQuse += c.UName + "|";
                    }
                }
            }
            return ResultList;
        }

        public List<AllotMapClass> GetAllotMap(short Cmx, short Ymx)
        {
            int iCount = DC.Origin.Where(y => y.Cmx == Cmx).Count();
            ulong uSectQuse = ulong.Parse(iCount.ToString() + "000000000000000000");

            List<AllotMapClass> ResultList = new List<AllotMapClass>();
            ////构建指定船舶的全部铺位
            //var Res1 = from i in DC.Cabin
            //           join g in DC.Grade on i.Gmx equals g.Gmx
            //           where g.Ymx == Ymx
            //           select new
            //           {
            //               Gmx = g.Gmx,
            //               GName = g.Nym,
            //               IName = i.Nym,
            //               First = i.First,
            //               Last = i.Last
            //           };

            //foreach (var c in Res1)
            //{
            //    for (short x = c.First; x <= c.Last; x++)
            //    {
            //        AllotMapClass ccc = new AllotMapClass
            //        {
            //            Gmx = c.Gmx,
            //            GName = c.GName,
            //            IName = c.IName,
            //            Bnum = x,
            //            SectQuse = uSectQuse,
            //            AgentQuse = ""
            //        };
            //        ResultList.Add(ccc);
            //    }
            //}

            //构建指定船舶的全部铺位
            var Res1 = from b in DC.Lbunk
                       join i in DC.Cabin on b.Cmx equals i.Cmx
                       join g in DC.Grade on i.Gmx equals g.Gmx
                       join c in DC.Course on b.Lmx equals c.Cmx
                       where (g.Ymx == Ymx)
                       && (c.Cmx == Cmx)
                       orderby g.Gmx, i.Cmx, b.Bnum
                       select new
                       {
                           Gmx = g.Gmx,
                           GName = g.Nym,
                           IName = i.Nym,
                           Bnum = b.Bnum
                       };

            foreach (var c in Res1)
            {
                AllotMapClass ccc = new AllotMapClass
                {
                    Gmx = c.Gmx,
                    GName = c.GName,
                    IName = c.IName,
                    Bnum = c.Bnum,
                    SectQuse = uSectQuse,
                    AgentQuse = ""
                };
                ResultList.Add(ccc);
            }

            //根据留额预设，更新铺位的航段留额信息
            var ResAll = from t in DC.Allot
                         join u in DC.Quse on t.Umx equals u.Umx
                         join a in DC.Agent on u.Amx equals a.Amx
                         join p in DC.Gprice on t.Pmx equals p.Pmx
                         join f in DC.Finish on p.Fmx equals f.Fmx
                         join o in DC.Origin on f.Omx equals o.Omx
                         join c in DC.Course on o.Cmx equals c.Cmx
                         join g in DC.Grade on p.Gmx equals g.Gmx
                         join y in DC.Yacht on g.Ymx equals y.Ymx
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         join wf in DC.Wharf on f.Wmx equals wf.Wmx
                         where (c.Cmx == Cmx)
                         && (y.Ymx == Ymx)
                         select new
                         {
                             aFirst = t.First,
                             aLast = t.Last,
                             UName = a.Nym + u.Nym,
                             Gmx = g.Gmx,
                             Fmx = f.Fmx
                         };

            foreach (var c in ResAll)
            {
                ulong uAddSectQuse = AddSectQuse(c.Fmx, false); 
                for (short x = c.aFirst; x <= c.aLast; x++)
                {
                    var ResBunk = ResultList.Where(y => y.Gmx == c.Gmx && y.Bnum == x);
                    //每个等级的留额预设只指定起始终止号，没有对等级铺位进行判断，这里需判断是否有此铺位
                    if (ResBunk.Count() > 0)
                    {
                        ResBunk.First().SectQuse += uAddSectQuse;
                        ResBunk.First().AgentQuse += c.UName + "|";
                    }
                }
            }

            return ResultList;
        }

        public List<AllotClass> GetAllot(short Cmx, short Ymx, short Amx)
        {
            List<AllotClass> ResultList = new List<AllotClass>();
            var Result = from t in DC.Allot
                         join u in DC.Quse on t.Umx equals u.Umx
                         join a in DC.Agent on u.Amx equals a.Amx
                         join p in DC.Gprice on t.Pmx equals p.Pmx
                         join f in DC.Finish on p.Fmx equals f.Fmx
                         join o in DC.Origin on f.Omx equals o.Omx
                         join c in DC.Course on o.Cmx equals c.Cmx
                         join g in DC.Grade on p.Gmx equals g.Gmx
                         join y in DC.Yacht on g.Ymx equals y.Ymx
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         join wf in DC.Wharf on f.Wmx equals wf.Wmx
                         where (c.Cmx == Cmx || Cmx == 0)
                         && (a.Amx == Amx || Amx == 0)
                         && (y.Ymx == Ymx || Ymx == 0)
                         select new
                         {
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             Amx = a.Amx,
                             CName = c.Nym,
                             YName = y.Nym,
                             AName = a.Nym,
                             UName = a.Nym + u.Nym,
                             Gmx = g.Gmx,
                             GName = g.Nym,
                             FromW = wo.Nym,
                             Fmx = f.Fmx,
                             DestW = wf.Nym,
                             First = t.First,
                             Last = t.Last
                         };

            foreach (var c in Result)
            {
                AllotClass ccc = new AllotClass
                {
                    CY = c.Cmx.ToString() + "|" + c.Ymx.ToString(),
                    CYName = c.CName + "|" + c.YName,
                    Amx = c.Amx.ToString(),
                    AName = c.AName,
                    UName = c.UName,
                    Gmx = c.Gmx,
                    GName = c.GName,
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Totem = GetTotem(c.Fmx),
                    First = c.First,
                    Last = c.Last,
                    QuseCount = c.Last - c.First + 1
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<GPriceClass> GetGPrice(short Cmx, short Ymx)
        {
            List<GPriceClass> ResultList = new List<GPriceClass>();
            var Result = from p in DC.Gprice 
                         join f in DC.Finish on p.Fmx equals f.Fmx
                         join o in DC.Origin on f.Omx equals o.Omx
                         join c in DC.Course on o.Cmx equals c.Cmx
                         join g in DC.Grade on p.Gmx equals g.Gmx
                         join y in DC.Yacht on g.Ymx equals y.Ymx
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         join wf in DC.Wharf on f.Wmx equals wf.Wmx
                         where (c.Cmx == Cmx || Cmx == 0)
                         && (y.Ymx == Ymx || Ymx == 0)
                         orderby c.Cmx,y.Ymx,g.Gmx,o.Omx,f.Fmx
                         select new
                         {
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             CName = c.Nym,
                             YName = y.Nym,
                             Gmx = g.Gmx,
                             GName = g.Nym,
                             FromID = wo.Wmx,
                             FromW = wo.Nym,
                             Fmx = f.Fmx,
                             DestID = wf.Wmx,
                             DestW = wf.Nym,
                             Fare1 = p.Fare1,
                             Fare2 = p.Fare2,
                             Fare3 = p.Fare3
                         };

            foreach (var c in Result)
            {
                GPriceClass ccc = new GPriceClass
                {
                    Cmx = c.Cmx,
                    Ymx = c.Ymx,
                    CName = c.CName,
                    YName = c.YName,
                    Gmx = c.Gmx,
                    GName = c.GName,
                    SectID = c.FromID.ToString() + "-" + c.DestID.ToString(),
                    SectName = c.FromW + "-" + c.DestW,
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Totem = GetTotem(c.Fmx),
                    Fare1 = c.Fare1,
                    Fare2 = c.Fare2,
                    Fare3 = c.Fare3
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<MPriceClass> GetMPrice(short Cmx, short Ymx)
        {
            List<MPriceClass> ResultList = new List<MPriceClass>();
            var Result = from p in DC.Mprice
                         join f in DC.Finish on p.Fmx equals f.Fmx
                         join o in DC.Origin on f.Omx equals o.Omx
                         join c in DC.Course on o.Cmx equals c.Cmx
                         join t in DC.Motype on p.Tmx equals t.Tmx
                         join y in DC.Yacht on t.Ymx equals y.Ymx
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         join wf in DC.Wharf on f.Wmx equals wf.Wmx
                         where (c.Cmx == Cmx || Cmx == 0)
                         && (y.Ymx == Ymx || Ymx == 0)
                         select new
                         {
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             CName = c.Nym,
                             YName = y.Nym,
                             Tmx = t.Tmx,
                             TName = t.Tnym,
                             FromID = wo.Wmx,
                             FromW = wo.Nym,
                             Fmx = f.Fmx,
                             DestID = wf.Wmx,
                             DestW = wf.Nym,
                             Fare1 = p.Fare1,
                             Fare2 = p.Fare2
                         };

            foreach (var c in Result)
            {
                MPriceClass ccc = new MPriceClass
                {
                    Cmx = c.Cmx,
                    Ymx = c.Ymx,
                    CName = c.CName,
                    YName = c.YName,
                    Tmx = c.Tmx,
                    TName = c.TName,
                    SectID = c.FromID.ToString() + "-" + c.DestID.ToString(),
                    SectName = c.FromW + "-" + c.DestW,
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Totem = GetTotem(c.Fmx),
                    Fare1 = c.Fare1,
                    Fare2 = c.Fare2
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public int GetLBunkCount(short Cmx, short Ymx, int Ver)
        {
            int iResult = 0;
            var Result = from b in DC.Lbunk
                         join i in DC.Cabin on b.Cmx equals i.Cmx
                         join g in DC.Grade on i.Gmx equals g.Gmx
                         join y in DC.Yacht on g.Ymx equals y.Ymx
                         join c in DC.Course on b.Lmx equals c.Cmx
                         where (c.Cmx == Cmx)
                         && (y.Ymx == Ymx)
                         orderby c.Cmx, y.Ymx, g.Gmx, i.Cmx, b.Bnum
                         select new
                         {
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             CName = c.Nym,
                             YName = y.Nym,
                             GName = g.Nym,
                             IName = i.Nym,
                             Bnum = b.Bnum,
                             Ver1 = b.Ver1,
                             Ver2 = b.Ver2
                         };
            if (Ver == 1)
                iResult = Result.Count(y=>y.Ver1 == true);
            else if (Ver == 2)
                iResult = Result.Count(y => y.Ver2 == true);
            else
                iResult = Result.Count();
            return iResult;
        }

        public List<LBunkClass> GetLBunk(short Cmx, short Ymx)
        {
            List<LBunkClass> ResultList = new List<LBunkClass>();
            var Result = from b in DC.Lbunk
                         join i in DC.Cabin on b.Cmx equals i.Cmx
                         join g in DC.Grade on i.Gmx equals g.Gmx
                         join y in DC.Yacht on g.Ymx equals y.Ymx
                         join c in DC.Course on b.Lmx equals c.Cmx
                         where (c.Cmx == Cmx)
                         && (y.Ymx == Ymx)
                         orderby c.Cmx,y.Ymx,g.Gmx,i.Cmx,b.Bnum
                         select new
                         {
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             CName = c.Nym,
                             YName = y.Nym,
                             GName = g.Nym,
                             IName = i.Nym,
                             Bnum = b.Bnum,
                             Ver1 = b.Ver1,
                             Ver2 = b.Ver2
                         };

            foreach (var c in Result)
            {
                LBunkClass ccc = new LBunkClass
                {
                    Cmx = c.Cmx,
                    Ymx = c.Ymx,
                    CName = c.CName,
                    YName = c.YName,
                    GName = c.GName,
                    IName = c.IName,
                    Bnum = c.Bnum,
                    Ver1 = c.Ver1,
                    Ver2 = c.Ver2
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<PubVoyageClass> GetPubVoyage(string LeaveTime)
        {
            var ResVoy = from v in DC.Voyage
                         join o in DC.Wharfrom on v.Vnx equals o.Vnx
                         join l in DC.Liner on v.Lmx equals l.Lmx
                         join y in DC.Yacht on l.Ymx equals y.Ymx
                         join c in DC.Course on l.Cmx equals c.Cmx
                         where (SqlMethods.DateDiffDay(o.Leave, DateTime.Parse(LeaveTime == "" ? "1900-1-1" : LeaveTime)) == 0)
                         select new
                         {
                             Vnx = v.Vnx,
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             Wmx = o.Wmx,
                             CName = c.Nym,
                             YName = y.Nym,
                             Sail = v.Sail,
                             Sell = v.Sell,
                             Leave = o.Leave
                         };

            List<PubVoyageClass> TempList = new List<PubVoyageClass>();
            foreach (var f in ResVoy)
            {
                Dictionary<int, short> cDict = GetVoyageWharfDicByVnx(f.Vnx);
                if (f.Wmx == cDict[cDict.Count - 1])
                {
                    PubVoyageClass ccc = new PubVoyageClass
                    {
                        Cmx = f.Cmx,
                        Ymx = f.Ymx,
                        CName = f.CName,
                        YName = f.YName,
                        FullCourse = GetFullCourseByVnx(f.Vnx),
                        Sail = f.Sail,
                        Sell = f.Sell,
                        Leave = f.Leave.ToString("MM-dd HH:mm"),
                        SellCount = VoyageSellFitCount(f.Vnx)
                    };
                    TempList.Add(ccc);
                }
            }
            return TempList;
        }

        public string GetFullCourseByCmx(short Cmx)
        {
            Dictionary<int, short> cDict = GetWharfDicByCmx(Cmx);
            //处理航线所经码头
            string sWharfs = "";
            string sSplit = "-";
            for (int i = cDict.Count() - 1; i >= 0; i--)
            {
                if (i == 0) sSplit = "";
                sWharfs += DC.Wharf.ToDictionary(y=>y.Wmx)[cDict[i]].Nym + sSplit;
            }
            return sWharfs;
        }

        public string GetFullCourseByVnx(int Vnx)
        {
            Dictionary<int, short> cDict = GetVoyageWharfDicByVnx(Vnx);
            //处理航线所经码头
            string sWharfs = "";
            string sSplit = "-";
            for (int i = cDict.Count() - 1; i >= 0; i--)
            {
                if (i == 0) sSplit = "";
                sWharfs += DC.Wharf.ToDictionary(y => y.Wmx)[cDict[i]].Nym + sSplit;
            }
            return sWharfs;
        }

        public List<VoyageClass> GetVoyage(string LeaveTime, short Wmx, short Ymx)
        {
            //Wmx在此只起到保持记录唯一的作用
            List<VoyageClass> ResultList = new List<VoyageClass>();
            var Result = from v in DC.Voyage
                         join o in DC.Wharfrom on v.Vnx equals o.Vnx
                         join l in DC.Liner on v.Lmx equals l.Lmx
                         join y in DC.Yacht on l.Ymx equals y.Ymx
                         join c in DC.Course on l.Cmx equals c.Cmx
                         where (SqlMethods.DateDiffDay(o.Leave, DateTime.Parse(LeaveTime == "" ? "1900-1-1" : LeaveTime)) == 0 || LeaveTime == "")
                         && (o.Wmx == Wmx || Wmx == 0)
                         && (y.Ymx == Ymx || Ymx == 0)
                         select new
                         {
                             Vnx = v.Vnx,
                             Cmx = c.Cmx,
                             Ymx = y.Ymx,
                             CName = c.Nym,
                             YName = y.Nym,
                             Vnum = v.Vnum,
                             Sail = v.Sail,
                             Sell = v.Sell,
                             Pver = v.Pver,
                             Bver = v.Bver,
                             Mver = v.Mver,
                             BookFee = v.BookFee,
                             Rate = v.Rate
                         };

            foreach (var c in Result.Distinct())
            {
                VoyageClass ccc = new VoyageClass
                {
                    Vnx = c.Vnx,
                    Cmx = c.Cmx,
                    Ymx = c.Ymx,
                    CName = c.CName,
                    YName = c.YName,
                    Vnum = c.Vnum,
                    Sail = c.Sail,
                    Sell = c.Sell,
                    Pver = c.Pver.ToString() + "#",
                    Bver = c.Bver.ToString() + "#",
                    Mver = c.Mver.ToString() + "#",
                    BookFee = c.BookFee,
                    Rate = c.Rate
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<VoyagentClass> GetVoyagent(int Vnx)
        {
            List<VoyagentClass> ResultList = new List<VoyagentClass>();
            var Result = from f in DC.Voyagent
                         join a in DC.Agent on f.Amx equals a.Amx
                         join o in DC.Wharfrom on f.Fnx equals o.Fnx
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         join v in DC.Voyage on o.Vnx equals v.Vnx
                         join l in DC.Liner on v.Lmx equals l.Lmx
                         join y in DC.Yacht on l.Ymx equals y.Ymx
                         join c in DC.Course on l.Cmx equals c.Cmx
                         where (v.Vnx == Vnx || Vnx == 0)
                         select new
                         {
                             FromW = wo.Nym,
                             AName = a.Nym,
                             Tdwn = f.Tdwn,
                             Qdwn = f.Qdwn,
                             Rdwn = f.Rdwn,
                             Credit = f.Credit
                         };

            foreach (var c in Result)
            {
                VoyagentClass ccc = new VoyagentClass
                {
                    FromW = c.FromW,
                    AName = c.AName,
                    Tdwn = c.Tdwn + "分钟",
                    Qdwn = c.Qdwn + "小时",
                    Rdwn = c.Rdwn + "分钟",
                    RetX = (c.Credit & 8) == 0 ? false : true,
                    Tcar = (c.Credit & 4) == 0 ? false : true,
                    Tbag = (c.Credit & 2) == 0 ? false : true,
                    Retn = (c.Credit & 1) == 0 ? false : true,
                };
                ResultList.Add(ccc);
            }
            return ResultList;

        }

        public List<WharfinyClass> GetWharfiny(int Fnx)
        {
            List<WharfinyClass> ResultList = new List<WharfinyClass>();
            var Result = DC.Wharfiny.Where(y => y.Fnx == Fnx);
            foreach (var c in Result)
            {
                WharfinyClass ccc = new WharfinyClass
                {
                    DestW = DC.Wharf.ToDictionary(y=>y.Wmx)[c.Wmx].Nym,
                    Fare = c.Fare,
                    LuggFee = c.LuggFee,
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<WharfromClass> GetWharfrom(int Vnx)
        {
            List<WharfromClass> ResultList = new List<WharfromClass>();
            var Result = from o in DC.Wharfrom 
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         where (o.Vnx == Vnx)
                         select new
                         {
                             Fnx = o.Fnx,
                             FromW = wo.Nym,
                             Leave = o.Leave,
                             Delay = o.Delay,
                             Topn = o.Topn,
                             Ready = o.Ready,
                             Wharfee = o.Wharfee,
                             Wfee = o.Wfee,
                             Busfee = o.Busfee,
                             Extras = o.Extras
                         };

            foreach (var c in Result)
            {
                WharfromClass ccc = new WharfromClass
                {
                    Fnx = c.Fnx,
                    FromW = c.FromW,
                    Leave = c.Leave.ToString("MM-dd HH:mm"),
                    Topn = c.Topn.ToString("MM-dd HH:mm"),
                    Delay = c.Delay + "分钟",
                    Ready = c.Ready,
                    Wharfee = c.Wharfee,
                    Wfee = c.Wfee,
                    Busfee = c.Busfee,
                    Extras = c.Extras
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<GFareClass> GetGFare(int Vnx)
        {
            List<GFareClass> ResultList = new List<GFareClass>();
            var Result = from f in DC.Gfare
                         join g in DC.Grade on f.Gmx equals g.Gmx
                         join wy in DC.Wharfiny on f.Ynx equals wy.Ynx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join wwy in DC.Wharf on wy.Wmx equals wwy.Wmx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         where wf.Vnx == Vnx
                         orderby g.Gmx, wf.Fnx, wy.Ynx
                         select new
                         {
                             Ynx = wy.Ynx,
                             Gmx = g.Gmx,
                             GName = g.Nym,
                             FromID = wf.Fnx,
                             FromW = wwf.Nym,
                             DestID = wy.Ynx,
                             DestW = wwy.Nym,
                             Fare = f.Fare,
                             Discnt = f.Discnt,
                             Rate = f.Rate,
                             BunkFee = f.BunkFee
                         };

            foreach (var c in Result)
            {
                GFareClass ccc = new GFareClass
                {
                    Gmx = c.Gmx,
                    GName = c.GName,
                    SectID = c.FromID.ToString() + "-" + c.DestID.ToString(),
                    SectName = c.FromW + "-" + c.DestW,
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Totem = GetVoyageTotem(c.Ynx),
                    Fare = c.Fare,
                    Discnt = c.Discnt,
                    Rate = c.Rate,
                    BunkFee = c.BunkFee
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<MFareClass> GetMFare(int Vnx)
        {
            List<MFareClass> ResultList = new List<MFareClass>();
            var Result = from f in DC.Mfare
                         join t in DC.Motype on f.Tmx equals t.Tmx
                         join wy in DC.Wharfiny on f.Ynx equals wy.Ynx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join wwy in DC.Wharf on wy.Wmx equals wwy.Wmx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         where wf.Vnx == Vnx
                         orderby t.Tmx, wf.Fnx, wy.Ynx
                         select new
                         {
                             Ynx = wy.Ynx,
                             Tmx = t.Tmx,
                             TName = t.Tnym,
                             FromID = wf.Fnx,
                             FromW = wwf.Nym,
                             DestID = wy.Ynx,
                             DestW = wwy.Nym,
                             Fare = f.Fare,
                             Rate = f.Rate,
                             RollFee = f.RollFee
                         };

            foreach (var c in Result)
            {
                MFareClass ccc = new MFareClass
                {
                    Tmx = c.Tmx,
                    TName = c.TName,
                    SectID = c.FromID.ToString() + "-" + c.DestID.ToString(),
                    SectName = c.FromW + "-" + c.DestW,
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Totem = GetVoyageTotem(c.Ynx),
                    Fare = c.Fare,
                    Rate = c.Rate,
                    RollFee = c.RollFee
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        //包含售、配票数
        private int VoyageSellFitCount(int Vnx)
        {
            var Result = from f in DC.Wharfiny
                         join o in DC.Wharfrom on f.Fnx equals o.Fnx
                         join wf in DC.Wharf on f.Wmx equals wf.Wmx
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         join v in DC.Voyage on o.Vnx equals v.Vnx
                         join l in DC.Liner on v.Lmx equals l.Lmx
                         join y in DC.Yacht on l.Ymx equals y.Ymx
                         join g in DC.Grade on y.Ymx equals g.Ymx
                         where (v.Vnx == Vnx)
                         orderby g.Gmx, wo.Wmx, wf.Wmx
                         select new
                         {
                             Gmx = g.Gmx,
                             Ynx = f.Ynx,
                             Vnx = v.Vnx,
                             GName = g.Nym,
                             FromW = wo.Nym,
                             DestW = wf.Nym,
                         };

            int iResult = 0;
            foreach (var c in Result)
            {
                iResult += VoyageSelloutCount(c.Gmx, c.Ynx, 0) + VoyageBookCount(c.Gmx, c.Ynx, true);
            }
            return iResult;
        }

        public List<VoyageStatusClass> GetVoyageStatus(int Vnx)
        {
            List<VoyageStatusClass> ResultList = new List<VoyageStatusClass>();
            var Result = from f in DC.Wharfiny
                         join o in DC.Wharfrom on f.Fnx equals o.Fnx
                         join wf in DC.Wharf on f.Wmx equals wf.Wmx
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         join v in DC.Voyage on o.Vnx equals v.Vnx
                         join l in DC.Liner on v.Lmx equals l.Lmx
                         join y in DC.Yacht on l.Ymx equals y.Ymx
                         join g in DC.Grade on y.Ymx equals g.Ymx
                         where (v.Vnx == Vnx)
                         orderby g.Gmx,wo.Wmx,wf.Wmx
                         select new
                         {
                             Gmx = g.Gmx,
                             Ynx = f.Ynx,
                             Vnx = v.Vnx,
                             GName = g.Nym,
                             FromW = wo.Nym,
                             DestW = wf.Nym,
                         };

            int iCount = GetSectCount(Vnx);
            foreach (var c in Result)
            {
                VoyageStatusClass ccc = new VoyageStatusClass
                {
                    GName = c.GName,
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Flag = DC.Gfare.Where(y=>y.Ynx == c.Ynx).First().Flag.ToString() + "|" + iCount.ToString(),
                    PrimeCount = VoyagePrimeCount(c.Gmx, c.Vnx),
                    QuseCount = VoyageQuseCount(c.Gmx, c.Ynx),
                    BookCount = VoyageBookCount(c.Gmx,c.Ynx,false).ToString() + "(" + 
                                VoyageBookCount(c.Gmx, c.Ynx, true).ToString() + ")",
                    SellCount = VoyageSelloutCount(c.Gmx, c.Ynx, 0).ToString() + "(" +
                                VoyageSelloutCount(c.Gmx, c.Ynx, 1).ToString() + "|" +
                                VoyageSelloutCount(c.Gmx, c.Ynx, -1).ToString() + ")",
                    RestCount = VoyageRestCount(c.Gmx, c.Ynx, c.Vnx)
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        private int VoyagePrimeCount(short Gmx, int Vnx)
        {
            var Result = from b in DC.Vbunk
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         where (c.Gmx == Gmx)
                         && (b.Vnx == Vnx)
                         select new
                         {
                             Mask = b.Mask
                         };
            return Result.Count();
        }

        private int VoyageRestCount(short Gmx, int Ynx, int Vnx)
        {
            int iFlag = DC.Gfare.Where(y => y.Gmx == Gmx && y.Ynx == Ynx).First().Flag;
            var Result = from b in DC.Vbunk
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         where (c.Gmx == Gmx)
                         && (b.Vnx == Vnx)
                         select new
                         {
                             Mask = b.Mask
                         };
            return Result.Count(y=> (y.Mask & iFlag) == 0);
        }

        private int VoyageQuseCount(short Gmx, int Ynx)
        {
            int iResult = 0;
            var Result = from l in DC.Vallot
                         join p in DC.Gfare on l.Pnx equals p.Pnx
                         join g in DC.Grade on p.Gmx equals g.Gmx
                         join wy in DC.Wharfiny on p.Ynx equals wy.Ynx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         where (g.Gmx == Gmx)
                         && (wy.Ynx == Ynx)
                         select new
                         {
                             Gmx = g.Gmx,
                             First = l.First,
                             Last = l.Last
                         };
            //处理留额铺位越界的问题，ReFirst和ReLast为起始铺位和终止铺位。
            foreach (var c in Result)
            {
                List<short> ff = new List<short>();
                List<short> ll = new List<short>();

                foreach (var a in DC.Cabin.Where(y => y.Gmx == c.Gmx))
                {
                    ff.Add(a.First);
                    ll.Add(a.Last);
                }
                short ReFirst = ff.Min();
                short ReLast = ll.Max();
                int ReCount = 0;
                if (c.Last >= ReFirst && c.Last >= c.First)
                {
                    if (c.Last >= ReFirst && c.Last <= ReLast)
                        ReCount = c.Last - c.First + 1;
                    else if (c.First >= ReFirst && c.First <= ReLast)
                        ReCount = ReLast - c.First + 1;
                }
                iResult += ReCount;
            }
            return iResult;
        }

        private int VoyageSelloutCount(short Gmx, int Ynx, int HasBus)
        {
            int iResult = 0;
            var Result = from s in DC.Sellout
                         join l in DC.Lockup on s.Lnx equals l.Lnx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         where (l.Knx == null)
                         && (g.Gmx == Gmx)
                         && (wy.Ynx == Ynx)
                         select new {
                             Onx = s.Onx == null ? 0 : s.Onx,
                             Tnx = s.Tnx == null ? 0 : s.Tnx,
                             Rclerk = s.Rclerk == null ? "" : s.Rclerk,
                             Rtime = s.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1"))
                         };
            if (HasBus > 0)
                iResult = Result.Count(y => (y.Onx != 0 || y.Tnx != 0) && y.Rclerk == "" && SqlMethods.DateDiffDay(y.Rtime, DateTime.Parse("1900-1-1")) == 0);
            else if (HasBus < 0)
                iResult = Result.Count(y => y.Onx == 0 && y.Tnx == 0 && y.Rclerk == "" && SqlMethods.DateDiffDay(y.Rtime, DateTime.Parse("1900-1-1")) == 0);
            else
                iResult = Result.Count(y => y.Rclerk == "" && SqlMethods.DateDiffDay(y.Rtime, DateTime.Parse("1900-1-1")) == 0);

            return iResult;
        }

        private int VoyageBookCount(short Gmx,int Ynx,bool isFit)
        {
            //处理订票
            var ReBook = from l in DC.Lockup
                         join k in DC.Bookeep on l.Knx equals k.Knx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         where (k.Annul == null) //筛去退订的记录
                         && (wy.Ynx == Ynx)
                         && (g.Gmx == Gmx)
                         select new {
                             Hold = l.Hold.GetValueOrDefault(DateTime.Parse("1900-1-1"))
                         };
            return isFit ? ReBook.Count(y => SqlMethods.DateDiffDay(y.Hold, DateTime.Parse("1900-1-1")) == 0) : ReBook.Count();
        }

        public List<StoplaceClass> GetStoplace(int Vnx)
        {
            List<StoplaceClass> ResultList = new List<StoplaceClass>();
            var Result = from f in DC.Stoplace
                         join b in DC.Bustop on f.Bmx equals b.Bmx
                         join o in DC.Wharfrom on f.Fnx equals o.Fnx
                         join wo in DC.Wharf on o.Wmx equals wo.Wmx
                         join v in DC.Voyage on o.Vnx equals v.Vnx
                         join l in DC.Liner on v.Lmx equals l.Lmx
                         join y in DC.Yacht on l.Ymx equals y.Ymx
                         join c in DC.Course on l.Cmx equals c.Cmx
                         where (v.Vnx == Vnx || Vnx == 0)
                         select new
                         {
                             FromW = wo.Nym,
                             BName = b.Bnym,
                             Leave = f.Leave,
                             Fare = f.Fare,
                             Stopay = f.Stopay,
                             Extras = f.Extras
                         };

            foreach (var c in Result)
            {
                StoplaceClass ccc = new StoplaceClass
                {
                    FromW = c.FromW,
                    BName = c.BName,
                    Leave = c.Leave.ToString("MM-dd HH:mm"),
                    Fare = c.Fare,
                    Stopay = c.Stopay,
                    Extras = c.Extras
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        //航运全价 = （舱位基本价 + 舱位软票费 + 码头空调费 + 港务费）-〉四舍五入取整 
        //航运折扣价 = （舱位基本价 x (1 - 舱位折扣率) + 舱位软票费 + 码头空调费 + 港务费）-〉四舍五入取整 
        private float doShip(byte DiscntType, float Discnt,float GFare, float BunkFee, float WharfFee, float WharfAir)
        {
            return DiscntType > 0 ? (float)Math.Floor((double)(GFare * (1 - Discnt) + BunkFee + WharfFee + WharfAir) + 0.5) :
                                (float)Math.Floor((double)(GFare + BunkFee + WharfFee + WharfAir) + 0.5);
        }

        //等级票价计算公式
        //舱位全价（包含本地车）= 航运全价 + 车站空调费 + 站务费 + 本地车费 
        //舱位全价（包含异地车）= 航运全价 + 异地车费 
        //舱位折扣价（包含本地车）= 航运折扣价 + 车站空调费 + 站务费 + 本地车费 
        //舱位折扣价（包含异地车）= 航运折扣价 + 异地车费 
        //舱位全价（包含本地车和异地车）= 航运全价 + 车站空调费 + 站务费 + 本地车费 + 异地车费 
        //舱位折扣价（包含本地车和异地车）= 航运折扣价 + 车站空调费 + 站务费 + 本地车费 + 异地车费
        private float doSellout(byte DiscntType, float Discnt,float GFare, float BunkFee, float WharfFee, float WharfAir,
                        float OFare, float TFare, float StopFee, float StopAir)
        {
            return doShip(DiscntType,Discnt,GFare,BunkFee,WharfFee,WharfAir) +
                (OFare > 0 ? OFare + StopFee + StopAir : 0) + TFare;
        }
        //载车票价 = （载车基本价 + 车型附加费 + 载车软票费）-〉四舍五入取整
        private float doRollout(float Fare, float Plus, float RollFee)
        {
            return (float)Math.Floor((double)(Fare + Plus + RollFee));
        }

        //行李票价 = （行李单价 x 行李重量 + 舱位软票费）-〉四舍五入取整
        private float doLuggout(float Fare, float Heft, float LuggFee)
        {
            return (float)Math.Floor((double)(Fare * Heft + LuggFee));
        }
        //舱位退款 = 舱位售出价 - （舱位售出价进10 x 舱位退票费率）
        private float doSellref(byte DiscntType, float Discnt,float GFare, float BunkFee, float WharfFee, float WharfAir,
                float OFare, float TFare, float StopFee, float StopAir, bool Refull, float Rate)
        {
            float fSellout = doSellout(DiscntType, Discnt,GFare, BunkFee, WharfFee, WharfAir,
                                        OFare, TFare, StopFee, StopAir);
            return fSellout - To10(fSellout) * (Refull ? 0 : Rate);
        }
        //载车退款 = 载车售出价 - （载车售出价进10 x 载车退票费率）
        private float doRollref(float Fare, float Plus, float RollFee, bool Refull, float Rate)
        {
            float fRollout = doRollout(Fare, Plus, RollFee);
            return fRollout - To10(fRollout) * (Refull ? 0 : Rate);
        }
        //行李退款 = 行李售出价 - （行李售出价进10 x 行李退票费率）
        private float doLuggref(float Fare, float Heft, float LuggFee, bool Refull, float Rate)
        {
            float fLuggout = doLuggout(Fare, Heft, LuggFee);
            return fLuggout - To10(fLuggout) * (Refull ? 0 : Rate);
        }

        public List<SelloutClass> GetSellout(int Pnx,int Tnx,string Stime,int Vnx)
        {
            List<SelloutClass> ResultList = new List<SelloutClass>();
            var Result = from s in DC.Sellout
                         join l in DC.Lockup on s.Lnx equals l.Lnx
                         join o in DC.Stoplace on s.Onx equals o.Onx into o1
                         join t in DC.Stopoint on s.Tnx equals t.Tnx into t1
                         join sa in DC.Agent on s.Smx equals sa.Amx
                         join ra in DC.Agent on s.Rmx equals ra.Amx into ra1
                         join p in DC.Vpage on s.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join ln in DC.Liner on v.Lmx equals ln.Lmx
                         join yh in DC.Yacht on ln.Ymx equals yh.Ymx
                         join cs in DC.Course on ln.Cmx equals cs.Cmx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join wwy in DC.Wharf on wy.Wmx equals wwy.Wmx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         where (SqlMethods.DateDiffDay(s.Stime, DateTime.Parse(Stime == "" ? "1900-1-1" : Stime)) == 0 || Stime == "")
                         && (v.Vnx == Vnx || Vnx == 0)
                         && (b.Tnx == Tnx || Tnx == 0)
                         && (p.Pnx == Pnx || Pnx == 0)
                         from o2 in o1.DefaultIfEmpty()
                         from t2 in t1.DefaultIfEmpty()
                         from ra2 in ra1.DefaultIfEmpty()
                         select new
                         {
                             Knx = l.Knx.GetValueOrDefault(0),
                             Snx = s.Snx,
                             Cmx = cs.Cmx,
                             CName = cs.Nym,
                             Ymx = yh.Ymx,
                             YName = yh.Nym,
                             Vnx = v.Vnx,
                             Vnum = v.Vnum,
                             FromW = wwf.Nym,
                             DestW = wwy.Nym,
                             Grade = g.Nym,
                             Cabin = c.Nym,
                             Bnum = b.Bnum,
                             GFare = f.Fare,
                             DiscntType = l.Discnt,
                             Discnt = f.Discnt,
                             Rate = f.Rate,
                             OFare = o2 == null ? 0 : o2.Fare,
                             TFare = t2 == null ? 0 : t2.Fare,
                             SAmx = sa.Amx,
                             SAgent = sa.Nym,
                             Sclerk = s.Sclerk,
                             Stime = s.Stime,
                             Vabel = m.Vabel,
                             Pos = p.Pos,
                             RAgent = ra2 == null ? "" : ra2.Nym,
                             Rclerk = s.Rclerk == null ? "" : s.Rclerk,
                             Rtime = s.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             HasBookFee = s.Bookfee.GetValueOrDefault(false),
                             Refull = s.Refull.GetValueOrDefault(false),
                             BunkFee = f.BunkFee,
                             WharfFee = wf.Wharfee,
                             WharfAir = wf.Extras,
                             StopFee = o2 == null ? 0 : o2.Stopay,
                             StopAir = o2 == null ? 0 : o2.Extras
                         };

            foreach (var c in Result)
            {
                SelloutClass ccc = new SelloutClass
                {
                    Snx = c.Snx,
                    Cmx = c.Cmx,
                    CName = c.CName,
                    Ymx = c.Ymx,
                    YName = c.YName,
                    Vnx = c.Vnx,
                    Vnum = c.Vnum.ToString(),
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Grade = c.Grade,
                    Cabin = c.Cabin,
                    Bnum = c.Bnum,
                    Price = doSellout(c.DiscntType, c.Discnt, c.GFare,c.BunkFee,c.WharfFee, c.WharfAir, 
                            c.OFare, c.TFare,c.StopFee,c.StopAir).ToString() 
                            + "|" + c.OFare.ToString() + "|" + c.TFare.ToString(),
                    Discnt = c.Discnt,
                    SAmx = c.SAmx,
                    SAgent = c.SAgent,
                    Sclerk = Comn.AClerkDisplay(c.Sclerk),
                    Stime = c.Stime.ToString("MM-dd hh:mm:ss"),
                    Vabel = c.Vabel + c.Pos.ToString().PadLeft(3, '0'),
                    RAgent = c.RAgent,
                    Rclerk = Comn.AClerkDisplay(c.Rclerk),
                    Rtime = SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0 ? "" : c.Rtime.ToString("MM-dd hh:mm:ss"),
                    HasBookFee = c.HasBookFee,
                    Refund = c.RAgent != "" && c.Rclerk != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0 ?
                                doSellref(c.DiscntType, c.Discnt, c.GFare, c.BunkFee, c.WharfFee, c.WharfAir,
                            c.OFare, c.TFare, c.StopFee, c.StopAir, c.Refull, c.Rate) : 0,
                    COF = c.CName + " | " + c.FromW + " | " + c.DestW,
                    YGCB = c.YName + " | " + c.Grade + " | " + c.Cabin + " | " + c.Bnum

                };

                if (c.Knx == 0 && c.RAgent == "" && c.Rclerk == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "售";
                else if (c.Knx == 0 && c.RAgent == "" && c.Rclerk == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "抹";
                else if (c.Knx == 0 && c.RAgent != "" && c.Rclerk != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "退";
                else if (c.Knx != 0 && c.RAgent == "" && c.Rclerk == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "配票";
                else if (c.Knx != 0 && c.RAgent != "" && c.Rclerk != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "配退";
                else if (c.Knx != 0 && c.RAgent == "" && c.Rclerk == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "配抹";
                else if (c.RAgent == "" && c.Rclerk != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "检";
                else ccc.SaleType = "其他";

                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<BookeepClass> GetBookeep(string Stime,int Vnx)
        {
            //多重Left Join 
            List<BookeepClass> ResultList = new List<BookeepClass>();
            var Result = from l in DC.Lockup
                         join s in DC.Sellout on l.Lnx equals s.Lnx into s1
                         join k in DC.Bookeep on l.Knx equals k.Knx
                         join ka in DC.Agent on k.Amx equals ka.Amx
                         join o in DC.Stoplace on k.Onx equals o.Onx into o1
                         join t in DC.Stopoint on k.Tnx equals t.Tnx into t1
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join ln in DC.Liner on v.Lmx equals ln.Lmx
                         join yh in DC.Yacht on ln.Ymx equals yh.Ymx
                         join cs in DC.Course on ln.Cmx equals cs.Cmx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join wwy in DC.Wharf on wy.Wmx equals wwy.Wmx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         where (SqlMethods.DateDiffDay(k.Stime, DateTime.Parse(Stime == "" ? "1900-1-1" : Stime)) == 0 || Stime == "")
                         && (v.Vnx == Vnx || Vnx == 0)
                         from o2 in o1.DefaultIfEmpty()
                         from t2 in t1.DefaultIfEmpty()
                         from s2 in s1.DefaultIfEmpty()
                         join sa in DC.Agent on s2.Smx equals sa.Amx into sa1
                         join ra in DC.Agent on s2.Rmx equals ra.Amx into ra1
                         join p in DC.Vpage on s2.Pnx equals p.Pnx into p1
                         from sa2 in sa1.DefaultIfEmpty()
                         from ra2 in ra1.DefaultIfEmpty()
                         from p2 in p1.DefaultIfEmpty()
                         join m in DC.Volume on p2.Lnx equals m.Lnx into m1
                         from m2 in m1.DefaultIfEmpty()
                         select new
                         {
                             Knx = k.Knx,
                             Cmx = cs.Cmx,
                             CName = cs.Nym,
                             Ymx = yh.Ymx,
                             YName = yh.Nym,
                             Vnx = v.Vnx,
                             Vnum = v.Vnum,
                             FromW = wwf.Nym,
                             DestW = wwy.Nym,
                             Custom = k.Cname,
                             Phone = k.Phone,
                             KAmx = ka.Amx,
                             KAgent = ka.Nym,
                             Kclerk = k.Seller,
                             Ktime = k.Stime,
                             Atime = k.Annul.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Grade = g.Nym,
                             Cabin = c.Nym,
                             Bnum = b.Bnum,
                             GFare = f.Fare,
                             DiscntType = l.Discnt,
                             Discnt = f.Discnt,
                             Rate = f.Rate,
                             OFare = o2 == null ? 0 : o2.Fare,
                             TFare = t2 == null ? 0 : t2.Fare,
                             SAgent = sa2 == null ? "" : sa2.Nym,
                             Sclerk = s2.Sclerk == null ? "" : s2.Sclerk,
                             Stime = s2.Stime == null ? DateTime.Parse("1900-1-1") : s2.Stime,
                             Vabel = m2 == null ? "" : m2.Vabel,
                             Pos = p2 == null ? 0 : p2.Pos,
                             RAgent = ra2 == null ? "" : ra2.Nym,
                             Rclerk = s2.Rclerk == null ? "" : s2.Rclerk,
                             Rtime = s2.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             HasBookFee = s2.Bookfee.GetValueOrDefault(false),
                             Refull = s2.Refull.GetValueOrDefault(false),
                             BunkFee = f.BunkFee,
                             WharfFee = wf.Wharfee,
                             WharfAir = wf.Extras,
                             StopFee = o2 == null ? 0 : o2.Stopay,
                             StopAir = o2 == null ? 0 : o2.Extras
                         };

            foreach (var c in Result)
            {
                BookeepClass ccc = new BookeepClass
                {
                    Knx = c.Knx,
                    Cmx = c.Cmx,
                    CName = c.CName,
                    Ymx = c.Ymx,
                    YName = c.YName,
                    Vnx = c.Vnx,
                    Vnum = c.Vnum.ToString(),
                    FromW = c.FromW,
                    DestW = c.DestW,
                    Custom = c.Custom,
                    Phone = c.Phone,
                    KAmx = c.KAmx,
                    KAgent = c.KAgent,
                    Kclerk = Comn.AClerkDisplay(c.Kclerk),
                    Ktime = c.Ktime.ToString("MM-dd hh:mm:ss"),
                    Atime = SqlMethods.DateDiffDay(c.Atime, DateTime.Parse("1900-1-1")) == 0 ? "" : c.Atime.ToString("MM-dd hh:mm:ss"),
                    Grade = c.Grade,
                    Cabin = c.Cabin,
                    Bnum = c.Bnum,
                    Price = doSellout(c.DiscntType, c.Discnt, c.GFare, c.BunkFee, c.WharfFee, c.WharfAir,
                            c.OFare, c.TFare, c.StopFee, c.StopAir).ToString() + "|"
                            + c.OFare.ToString() + "|" + c.TFare.ToString(),
                    Discnt = c.Discnt,
                    SAgent = c.SAgent,
                    Sclerk = Comn.AClerkDisplay(c.Sclerk),
                    Stime = SqlMethods.DateDiffDay(c.Stime, DateTime.Parse("1900-1-1")) == 0 ? "" : c.Stime.ToString("MM-dd hh:mm:ss"),
                    Vabel = c.Vabel == "" ? "" : c.Vabel + c.Pos.ToString().PadLeft(3, '0'),
                    RAgent = c.RAgent,
                    Rclerk = Comn.AClerkDisplay(c.Rclerk),
                    Rtime = SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0 ? "" : c.Rtime.ToString("MM-dd hh:mm:ss"),
                    HasBookFee = c.HasBookFee,
                    Refund = SqlMethods.DateDiffDay(c.Atime, DateTime.Parse("1900-1-1")) == 0 && c.Sclerk != "" &&
                             c.RAgent != "" && c.Rclerk != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0 ?
                                doSellref(c.DiscntType, c.Discnt, c.GFare, c.BunkFee, c.WharfFee, c.WharfAir,
                                c.OFare, c.TFare, c.StopFee, c.StopAir, c.Refull, c.Rate) : 0,
                    COF = c.CName + " | " + c.FromW + " | " + c.DestW,
                    YGCB = c.YName + " | " + c.Grade + " | " + c.Cabin + " | " + c.Bnum
                };

                if (SqlMethods.DateDiffDay(c.Atime, DateTime.Parse("1900-1-1")) == 0 && c.Sclerk == "" &&
                    c.RAgent == "" && c.Rclerk == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "订票";
                else if (SqlMethods.DateDiffDay(c.Atime, DateTime.Parse("1900-1-1")) != 0 && c.Sclerk == "" &&
                    c.RAgent == "" && c.Rclerk == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "退订";
                else if (SqlMethods.DateDiffDay(c.Atime, DateTime.Parse("1900-1-1")) == 0 && c.Sclerk != "" && 
                    c.RAgent == "" && c.Rclerk == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "配票";
                else if (SqlMethods.DateDiffDay(c.Atime, DateTime.Parse("1900-1-1")) == 0 && c.Sclerk != "" && 
                    c.RAgent != "" && c.Rclerk != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "配退";
                else if (SqlMethods.DateDiffDay(c.Atime, DateTime.Parse("1900-1-1")) == 0 && c.Sclerk != "" && 
                    c.RAgent == "" && c.Rclerk == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "配抹";
                else if (SqlMethods.DateDiffDay(c.Atime, DateTime.Parse("1900-1-1")) == 0 && c.Sclerk != "" && 
                    c.RAgent == "" && c.Rclerk != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "检";
                else ccc.SaleType = "其他";

                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<LuggoutClass> GetLuggout(int Pnx, string Stime, int Vnx)
        {
            List<LuggoutClass> ResultList = new List<LuggoutClass>();
            var Result = from g in DC.Luggout
                         join a in DC.Agent on g.Amx equals a.Amx
                         join p in DC.Vpage on g.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join wy in DC.Wharfiny on g.Ynx equals wy.Ynx
                         join wwy in DC.Wharf on wy.Wmx equals wwy.Wmx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         join v in DC.Voyage on wf.Vnx equals v.Vnx
                         join ln in DC.Liner on v.Lmx equals ln.Lmx
                         join yh in DC.Yacht on ln.Ymx equals yh.Ymx
                         join cs in DC.Course on ln.Cmx equals cs.Cmx
                         where (p.Pnx == Pnx || Pnx == 0)
                         && (v.Vnx == Vnx || Vnx == 0)
                         && (SqlMethods.DateDiffDay(g.Stime, DateTime.Parse(Stime == "" ? "1900-1-1" : Stime)) == 0 || Stime == "")
                         select new
                         {
                             Cmx = cs.Cmx,
                             CName = cs.Nym,
                             Ymx = yh.Ymx,
                             YName = yh.Nym,
                             Vnx = v.Vnx,
                             Vnum = v.Vnum,
                             FromW = wwf.Nym,
                             DestW = wwy.Nym,
                             SAmx = a.Amx,
                             SAgent = a.Nym,
                             Sclerk = g.Seller,
                             Stime = g.Stime,
                             Vabel = m.Vabel,
                             Pos = p.Pos,
                             Rtime = g.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Checker = g.Checker == null ? "" : g.Checker,
                             Fare = wy.Fare,
                             Heft = g.Heft,
                             Rate = v.Rate,
                             Refull = g.Refull.GetValueOrDefault(false),
                             LuggFee = wy.LuggFee
                         };

            foreach (var c in Result)
            {
                LuggoutClass ccc = new LuggoutClass
                {
                    Cmx = c.Cmx,
                    CName = c.CName,
                    Ymx = c.Ymx,
                    YName = c.YName,
                    Vnx = c.Vnx,
                    Vnum = c.Vnum.ToString(),
                    FromW = c.FromW,
                    DestW = c.DestW,
                    SAmx = c.SAmx,
                    SAgent = c.SAgent,
                    Sclerk = Comn.AClerkDisplay(c.Sclerk),
                    Stime = c.Stime.ToString("MM-dd hh:mm:ss"),
                    Vabel = c.Vabel + c.Pos.ToString().PadLeft(3, '0'),
                    Checker = Comn.AClerkDisplay(c.Checker),
                    Rtime = SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0 ? "" : c.Rtime.ToString("MM-dd hh:mm:ss"),
                    Fare = c.Fare,
                    Heft = c.Heft,
                    Price = doLuggout(c.Fare,c.Heft,c.LuggFee),
                    Refund = c.Checker != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0 ?
                                doLuggref(c.Fare,c.Heft,c.LuggFee, c.Refull, c.Rate) : 0
                };

                if (c.Checker == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "售";
                else if (c.Checker == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "抹";
                else if (c.Checker != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "退";
                else if (c.Checker != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "检";
                else ccc.SaleType = "其他";

                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<RolloutClass> GetRollout(int Pnx, string Stime, int Vnx)
        {
            List<RolloutClass> ResultList = new List<RolloutClass>();
            var Result = from r in DC.Rollout
                         join a in DC.Agent on r.Amx equals a.Amx
                         join p in DC.Vpage on r.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join f in DC.Mfare on r.Mnx equals f.Pnx
                         join t in DC.Motype on f.Tmx equals t.Tmx
                         join wy in DC.Wharfiny on f.Ynx equals wy.Ynx
                         join wwy in DC.Wharf on wy.Wmx equals wwy.Wmx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         join v in DC.Voyage on wf.Vnx equals v.Vnx
                         join ln in DC.Liner on v.Lmx equals ln.Lmx
                         join yh in DC.Yacht on ln.Ymx equals yh.Ymx
                         join cs in DC.Course on ln.Cmx equals cs.Cmx
                         where (p.Pnx == Pnx || Pnx == 0)
                         && (v.Vnx == Vnx || Vnx == 0)
                         && (SqlMethods.DateDiffDay(r.Stime, DateTime.Parse(Stime == "" ? "1900-1-1" : Stime)) == 0 || Stime == "")
                         select new
                         {
                             Cmx = cs.Cmx,
                             CName = cs.Nym,
                             Ymx = yh.Ymx,
                             YName = yh.Nym,
                             Vnx = v.Vnx,
                             Vnum = v.Vnum,
                             FromW = wwf.Nym,
                             DestW = wwy.Nym,
                             SAmx = a.Amx,
                             SAgent = a.Nym,
                             Sclerk = r.Seller,
                             Stime = r.Stime,
                             Vabel = m.Vabel,
                             Pos = p.Pos,
                             Rtime = r.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Checker = r.Checker == null ? "" : r.Checker,
                             MType = t.Tnym,
                             Fare = f.Fare,
                             Plus = r.Plus,
                             Plate = r.Plate,
                             Rate = f.Rate,
                             Refull = r.Refull.GetValueOrDefault(false),
                             RollFee = f.RollFee
                         };

            foreach (var c in Result)
            {
                RolloutClass ccc = new RolloutClass
                {
                    Cmx = c.Cmx,
                    CName = c.CName,
                    Ymx = c.Ymx,
                    YName = c.YName,
                    Vnx = c.Vnx,
                    Vnum = c.Vnum.ToString(),
                    FromW = c.FromW,
                    DestW = c.DestW,
                    SAmx = c.SAmx,
                    SAgent = c.SAgent,
                    Sclerk = Comn.AClerkDisplay(c.Sclerk),
                    Stime = c.Stime.ToString("MM-dd hh:mm:ss"),
                    Vabel = c.Vabel + c.Pos.ToString().PadLeft(3, '0'),
                    Checker = Comn.AClerkDisplay(c.Checker),
                    Rtime = SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0 ? "" : c.Rtime.ToString("MM-dd hh:mm:ss"),
                    MType = c.MType,
                    Fare = c.Fare,
                    Plus = c.Plus,
                    Plate = c.Plate,
                    Price = doRollout(c.Fare,c.Plus,c.RollFee),
                    Refund = c.Checker != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0 ? 
                                doRollref(c.Fare,c.Plus,c.RollFee,c.Refull,c.Rate) : 0
                    
                };
                if (c.Checker == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "售";
                else if (c.Checker == "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "抹";
                else if (c.Checker != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) != 0)
                    ccc.SaleType = "退";
                else if (c.Checker != "" && SqlMethods.DateDiffDay(c.Rtime, DateTime.Parse("1900-1-1")) == 0)
                    ccc.SaleType = "检";
                else ccc.SaleType = "其他"; 
                
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<VolumnClass> GetVolumn(string Account)
        {
            List<VolumnClass> ResultList = new List<VolumnClass>();
            var Result = from v in DC.Volume
                         join c in DC.Aclerk on v.Acnt equals c.Acnt
                         join a in DC.Agent on c.Amx equals a.Amx
                         where (c.Acnt == Account || Account == "")
                         select new
                         {
                             Lnx = v.Lnx,
                             Amx = a.Amx,
                             AName = a.Nym,
                             Acnt = c.Acnt,
                             Aclerk = Comn.AClerkDisplay(c.Acnt),
                             Draw = v.Draw,
                             Vabel = v.Vabel,
                             Ready = v.Ready
                         };

            foreach (var c in Result)
            {
                VolumnClass ccc = new VolumnClass
                {
                    Lnx = c.Lnx,
                    Amx = c.Amx,
                    AName = c.AName,
                    Acnt = c.Acnt,
                    Aclerk = c.Aclerk,
                    Draw = c.Draw.ToShortDateString(),
                    Vabel = c.Vabel,
                    Ready = c.Ready
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        public List<VpageClass> GetVpage(int Lnx)
        {
            List<VpageClass> ResultList = new List<VpageClass>();
            var Result = from p in DC.Vpage
                         join v in DC.Volume on p.Lnx equals v.Lnx
                         where v.Lnx == Lnx
                         orderby p.Pos
                         select new
                         {
                             Pnx = p.Pnx,
                             Vabel = v.Vabel,
                             Pos = p.Pos,
                             Scrap = p.Scrap,
                             Used = p.Used
                         };

            foreach (var c in Result)
            {
                VpageClass ccc = new VpageClass
                {
                    Pnx = c.Pnx,
                    Pos = c.Vabel + c.Pos.ToString().PadLeft(3,'0'),
                    Scrap = c.Scrap,
                    Used = c.Used
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }

        /*************************************************************************************/
        /********                          Matrix                                   **********/
        /*************************************************************************************/
        public List<MatrixClass> GetMatrix(string TypeName, string Key)
        {
            List<MatrixClass> ResultList = null;
            switch (TypeName)
            {
                case "QuseCountGA":
                    ResultList = GetQuseCountGA(Key);
                    break;
                case "QuseCountGW":
                    ResultList = GetQuseCountGW(Key);
                    break;
                case "SaleCountGA":
                    ResultList = GetSaleCountGA(Key);
                    break;
                case "SaleCountGWF":
                    ResultList = GetSaleCountGWF(Key);
                    break;
                case "SaleCountGWD":
                    ResultList = GetSaleCountGWD(Key);
                    break;
            }
            return ResultList;
        }
        //等级码头留额
        private List<MatrixClass> GetQuseCountGW(string Key)
        {
            List<MatrixClass> ResultList = new List<MatrixClass>();
            var Result = from l in DC.Vallot
                         join u in DC.Quse on l.Umx equals u.Umx
                         join a in DC.Agent on u.Amx equals a.Amx
                         join p in DC.Gfare on l.Pnx equals p.Pnx
                         join g in DC.Grade on p.Gmx equals g.Gmx
                         join wy in DC.Wharfiny on p.Ynx equals wy.Ynx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join wwf in DC.Wharf on wf.Wmx equals wwf.Wmx
                         where (wf.Vnx == int.Parse(Key))
                         select new
                         {
                             GName = g.Nym,
                             WName = wwf.Nym,
                             Quse = l.Last - l.First + 1
                         };
            var Res = from r in Result
                      group r by new
                      {
                          r.GName,
                          r.WName
                      }
                          into zz
                          select new
                          {
                              Key = zz.Key,
                              QuseCount = zz.Sum(y => y.Quse)
                          };

            foreach (var c in Res)
            {
                MatrixClass ccc = new MatrixClass
                {
                    XName = c.Key.WName,
                    YName = c.Key.GName,
                    Cross = c.QuseCount
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }
        //等级票点留额
        private List<MatrixClass> GetQuseCountGA(string Key)
        {
            List<MatrixClass> ResultList = new List<MatrixClass>();
            var Result = from l in DC.Vallot
                         join u in DC.Quse on l.Umx equals u.Umx
                         join a in DC.Agent on u.Amx equals a.Amx
                         join p in DC.Gfare on l.Pnx equals p.Pnx
                         join g in DC.Grade on p.Gmx equals g.Gmx
                         join wy in DC.Wharfiny on p.Ynx equals wy.Ynx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         where (wf.Vnx == int.Parse(Key))
                         select new
                         {
                             GName = g.Nym,
                             AName = a.Nym,
                             Quse = l.Last - l.First + 1
                         };
            var Res = from r in Result
                      group r by new
                      {
                          r.GName,
                          r.AName
                      }
                          into zz
                          select new
                          {
                              Key = zz.Key,
                              QuseCount = zz.Sum(y=>y.Quse)
                          };

            foreach (var c in Res)
            {
                MatrixClass ccc = new MatrixClass
                {
                    XName = c.Key.AName,
                    YName = c.Key.GName,
                    Cross = c.QuseCount
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }
        //等级票点售票
        private List<MatrixClass> GetSaleCountGA(string Key)
        {
            List<MatrixClass> ResultList = new List<MatrixClass>();
            var Result = from s in DC.Sellout
                         join l in DC.Lockup on s.Lnx equals l.Lnx
                         join sa in DC.Agent on s.Smx equals sa.Amx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         where v.Vnx == int.Parse(Key)
                         orderby g.Gmx, sa.Amx
                         select new
                         {
                             GName = g.Nym,
                             AName = sa.Nym
                         };

            var Res = from r in Result
                      group r by new
                      {
                          r.GName,
                          r.AName
                      }
                          into zz
                          select new
                          {
                              Key = zz.Key,
                              SaleCount = zz.Count()
                          };

            foreach (var c in Res)
            {
                MatrixClass ccc = new MatrixClass
                {
                    XName = c.Key.AName,
                    YName = c.Key.GName,
                    Cross = c.SaleCount
                };
                ResultList.Add(ccc);
            }
            return ResultList;
        }
        //等级起点码头售票
        private List<MatrixClass> GetSaleCountGWF(string Key)
        {
            //该航次所有起点终点的组合
            var ReSect = from y in DC.Wharfiny
                         join f in DC.Wharfrom on y.Fnx equals f.Fnx
                         where (f.Vnx == int.Parse(Key))
                         select new
                         {
                             FromW = f.Wmx,
                             DestW = y.Wmx
                         };
            //航次码头顺序字典
            Dictionary<int, short> dWharf = GetVoyageWharfDicByVnx(int.Parse(Key));
            //起点码头主键字典
            Dictionary<int, short> dFromW = new Dictionary<int, short>();
            int iPlus = 0;
            foreach (var c in ReSect)
            {
                if (!dFromW.ContainsValue(c.FromW))
                {
                    dFromW.Add(iPlus, c.FromW);
                    iPlus++;
                }
            }
            //起点码头FalgList字典，将起点码头所形成的航段的Flag值组成List
            Dictionary<short, List<int>> dFromList = new Dictionary<short, List<int>>();
            int iFlag = 0;
            for (int i = 0; i < dFromW.Count; i++)
            {
                List<int> FlagList = new List<int>();
                var ReFrom = ReSect.Where(y => y.FromW == dFromW[i]);
                foreach (var f in ReFrom)
                {
                    iFlag = MakeFlagNum(dWharf, f.FromW, f.DestW);
                    FlagList.Add(iFlag);
                }
                dFromList.Add(dFromW[i], FlagList);
            }
            //获取售票记录中的等级和Flag
            var Result = from s in DC.Sellout
                         join l in DC.Lockup on s.Lnx equals l.Lnx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         where v.Vnx == int.Parse(Key)
                         orderby g.Gmx
                         select new
                         {
                             GName = g.Nym,
                             Flag = l.Flag
                         };
            //创建Matrix的初始值，X为起点码头，Y为等级
            List<MatrixClass> ResultList = new List<MatrixClass>();
            foreach (var m in Result)
            {
                foreach (var n in dFromList)
                {
                    string strFromW = DC.Wharf.ToDictionary(y => y.Wmx)[n.Key].Nym;
                    if (ResultList.Where(y => y.YName == m.GName && y.XName == strFromW).Count() == 0)
                    {
                        MatrixClass ccc = new MatrixClass
                        {
                            XName = strFromW,
                            YName = m.GName,
                            Cross = 0
                        };
                        ResultList.Add(ccc);
                    }
                }
            }
            //如果起点码头的FlagList中包含售票记录中的Flag，则该起点码头的售票数加1
            foreach (var c in Result)
            {
                string sFromW = string.Empty;
                foreach (var l in dFromList)
                {
                    if (l.Value.Contains(c.Flag))
                    {
                        sFromW = DC.Wharf.ToDictionary(y => y.Wmx)[l.Key].Nym;
                    }
                }
                if (sFromW != string.Empty)
                {
                    ResultList.Where(y => y.YName == c.GName && y.XName == sFromW).First().Cross += 1;
                }
            }
            return ResultList;
        }
        //按航次获取起点码头的字典
        public Dictionary<short, string> GetWharfromDicByVnx(int Vnx)
        {
            Dictionary<short, string> DicResult = new Dictionary<short, string>();
            //该航次所有起点终点的组合
            var ReSect = from y in DC.Wharfiny
                         join f in DC.Wharfrom on y.Fnx equals f.Fnx
                         where (f.Vnx == Vnx)
                         select new
                         {
                             FromW = f.Wmx
                         };
            foreach (var c in ReSect.Distinct())
            {
                DicResult.Add(c.FromW, DC.Wharf.ToDictionary(y => y.Wmx)[c.FromW].Nym);
            }
            return DicResult;
        }
        //按起点统计各终点码头售票数
        private List<MatrixClass> GetSaleCountGWD(string Key)
        {
            int iVnx = int.Parse(Key.Split('|').ToArray()[0]);
            short sWmx = short.Parse(Key.Split('|').ToArray()[1]);

            //该航次指定起点开航的各终点的组合
            var ReSect = from y in DC.Wharfiny
                         join f in DC.Wharfrom on y.Fnx equals f.Fnx
                         where (f.Vnx == iVnx) 
                         && (f.Wmx == sWmx)
                         select new
                         {
                             DestW = y.Wmx
                         };
            //航次码头顺序字典
            Dictionary<int, short> dWharf = GetVoyageWharfDicByVnx(iVnx);
            //终点码头主键字典
            Dictionary<int, short> dDestW = new Dictionary<int, short>();
            int iPlus = 0;
            foreach (var c in ReSect)
            {
                if (!dDestW.ContainsValue(c.DestW))
                {
                    dDestW.Add(iPlus, c.DestW);
                    iPlus++;
                }
            }
            //终点码头Falg字典，保存与起点码头所形成的航段的Flag值
            Dictionary<short, int> dDestFlag = new Dictionary<short, int>();
            int iFlag = 0;
            for (int i = 0; i < dDestW.Count; i++)
            {
                iFlag = MakeFlagNum(dWharf, sWmx, dDestW[i]);
                dDestFlag.Add(dDestW[i], iFlag);
            }
            //获取售票记录中的等级和Flag
            var Result = from s in DC.Sellout
                         join l in DC.Lockup on s.Lnx equals l.Lnx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         where v.Vnx == iVnx
                         orderby g.Gmx
                         select new
                         {
                             GName = g.Nym,
                             Flag = l.Flag
                         };
            //创建Matrix的初始值，X为起点码头，Y为等级
            List<MatrixClass> ResultList = new List<MatrixClass>();
            foreach (var m in Result)
            {
                foreach (var n in dDestFlag)
                {
                    string strDestW = DC.Wharf.ToDictionary(y => y.Wmx)[n.Key].Nym;
                    if (ResultList.Where(y => y.YName == m.GName && y.XName == strDestW).Count() == 0)
                    {
                        MatrixClass ccc = new MatrixClass
                        {
                            XName = strDestW,
                            YName = m.GName,
                            Cross = 0
                        };
                        ResultList.Add(ccc);
                    }
                }
            }
            //如果起点码头的FlagList中包含售票记录中的Flag，则该起点码头的售票数加1
            foreach (var c in Result)
            {
                string sDestW = string.Empty;
                foreach (var l in dDestFlag)
                {
                    if (c.Flag == l.Value)
                    {
                        sDestW = DC.Wharf.ToDictionary(y => y.Wmx)[l.Key].Nym;
                    }
                }
                if (sDestW != string.Empty)
                {
                    ResultList.Where(y => y.YName == c.GName && y.XName == sDestW).First().Cross += 1;
                }
            }
            return ResultList;
        }

        /*************************************************************************************/
        /********                          Matrix                                   **********/
        /*************************************************************************************/

        public List<DaySelloutClass> GetDaySellout(short Amx, string OutDay, string Account)
        {
            var Result = from s in DC.Sellout
                         join l in DC.Lockup on s.Lnx equals l.Lnx
                         join o in DC.Stoplace on s.Onx equals o.Onx into o1
                         join t in DC.Stopoint on s.Tnx equals t.Tnx into t1
                         join sa in DC.Agent on s.Smx equals sa.Amx
                         join ra in DC.Agent on s.Rmx equals ra.Amx into ra1
                         join p in DC.Vpage on s.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         where (SqlMethods.DateDiffDay(s.Stime, DateTime.Parse(OutDay)) == 0)
                         && (sa.Amx == Amx)
                         && (s.Sclerk == Account || Account =="")
                         from o2 in o1.DefaultIfEmpty()
                         from t2 in t1.DefaultIfEmpty()
                         from ra2 in ra1.DefaultIfEmpty()
                         select new
                         {
                             Knx = l.Knx.GetValueOrDefault(0),
                             DiscntType = l.Discnt,
                             GFare = f.Fare,
                             Discnt = f.Discnt,
                             Rate = f.Rate,
                             OFare = o2 == null ? 0 : o2.Fare,
                             TFare = t2 == null ? 0 : t2.Fare,
                             SAgent = sa.Nym,
                             Sclerk = s.Sclerk,
                             Vabel = m.Vabel + p.Pos.ToString().PadLeft(3, '0'),
                             RAgent = ra2 == null ? "" : ra2.Nym,
                             Rclerk = s.Rclerk == null ? "" : s.Rclerk,
                             Rtime = s.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Bookfee = s.Bookfee.GetValueOrDefault(false),
                             Refull = s.Refull.GetValueOrDefault(false),
                             BunkFee = f.BunkFee,
                             WharfFee = wf.Wharfee,
                             WharfAir = wf.Extras,
                             StopFee = o2 == null ? 0 : o2.Stopay,
                             StopAir = o2 == null ? 0 : o2.Extras
                         };

            List<DaySelloutClass> TempList = new List<DaySelloutClass>();
            foreach (var r in Result)
            {
                DaySelloutClass ttt = new DaySelloutClass
                {
                    SClerk = r.Sclerk,
                    OneVabel = r.Vabel,
                    OutCount = (r.Knx == 0 && r.RAgent == "" && r.Rclerk == "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                                1 : 0,
                    OutFee = (r.Knx == 0 && r.RAgent == "" && r.Rclerk == "" &&
                              SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                              doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                r.OFare, r.TFare, r.StopFee, r.StopAir) : 0,
                    FitOutCount = (r.Knx != 0 && r.RAgent == "" && r.Rclerk == "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                                1 : 0,
                    FitOutFee = (r.Knx != 0 && r.RAgent == "" && r.Rclerk == "" &&
                              SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                              doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                r.OFare, r.TFare, r.StopFee, r.StopAir) : 0,
                    RtnCount = (r.Knx == 0 && r.RAgent != "" && r.Rclerk != "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    RtnFee = (r.Knx == 0 && r.RAgent != "" && r.Rclerk != "" &&
                              SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                              doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                    r.OFare, r.TFare, r.StopFee, r.StopAir, r.Refull, r.Rate) : 0,
                    FitRtnCount = (r.Knx != 0 && r.RAgent != "" && r.Rclerk != "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    FitRtnFee = (r.Knx != 0 && r.RAgent != "" && r.Rclerk != "" &&
                              SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                              doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                    r.OFare, r.TFare, r.StopFee, r.StopAir, r.Refull, r.Rate) : 0,
                    AnlCount = (r.Knx == 0 && r.RAgent == "" && r.Rclerk == "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    FitAnlCount = (r.Knx != 0 && r.RAgent == "" && r.Rclerk == "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0
                };
                TempList.Add(ttt);
            }

            List<string> ClerkList = new List<string>();
            foreach (var r in TempList)
            {
                if (!ClerkList.Contains(r.SClerk))
                    ClerkList.Add(r.SClerk);
            }

            List<DaySelloutClass> ResultList = new List<DaySelloutClass>();
            foreach (var c in ClerkList)
            {
                DaySelloutClass ccc = new DaySelloutClass
                {
                    SClerk = c,
                    OutCount = TempList.Where(y => y.SClerk == c).Sum(x => x.OutCount) +
                                TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount),
                    OutFee = TempList.Where(y => y.SClerk == c).Sum(x => x.OutFee) +
                                TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee),
                    RtnCount = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount),
                    RtnFee = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee),
                    FitOutCount = TempList.Where(y => y.SClerk == c).Sum(x => x.FitOutCount) +
                                    TempList.Where(y => y.SClerk == c).Sum(x => x.FitRtnCount),
                    FitOutFee = TempList.Where(y => y.SClerk == c).Sum(x => x.FitOutFee) +
                                    TempList.Where(y => y.SClerk == c).Sum(x => x.FitRtnFee),
                    FitRtnCount = TempList.Where(y => y.SClerk == c).Sum(x => x.FitRtnCount),
                    FitRtnFee = TempList.Where(y => y.SClerk == c).Sum(x => x.FitRtnFee),
                    OneVabel = TempList.Where(y => y.SClerk == c).Min(z => z.OneVabel),
                    EndVabel = TempList.Where(y => y.SClerk == c).Max(z => z.OneVabel),
                    AnlCount = TempList.Where(y => y.SClerk == c).Sum(x => x.AnlCount),
                    FitAnlCount = TempList.Where(y => y.SClerk == c).Sum(x => x.FitAnlCount),
                    Out = (TempList.Where(y => y.SClerk == c).Sum(x => x.OutCount) +
                            TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount)).ToString() + "|" +
                            (TempList.Where(y => y.SClerk == c).Sum(x => x.OutFee) +
                            TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee)).ToString(),
                    Rtn = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount).ToString() + "|" +
                           TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee).ToString(),
                    FitOut = (TempList.Where(y => y.SClerk == c).Sum(x => x.FitOutCount) +
                                TempList.Where(y => y.SClerk == c).Sum(x => x.FitRtnCount)).ToString() + "|" +
                                (TempList.Where(y => y.SClerk == c).Sum(x => x.FitOutFee) +
                                TempList.Where(y => y.SClerk == c).Sum(x => x.FitRtnFee)).ToString(),
                    FitRtn = (TempList.Where(y => y.SClerk == c).Sum(x => x.FitRtnCount)).ToString() + "|" + 
                                (TempList.Where(y => y.SClerk == c).Sum(x => x.FitRtnFee)).ToString()
                };
                ResultList.Add(ccc);
            }
            //处理订票
            var ReBook = from l in DC.Lockup
                         join k in DC.Bookeep on l.Knx equals k.Knx
                         join ka in DC.Agent on k.Amx equals ka.Amx
                         join o in DC.Stoplace on k.Onx equals o.Onx into o1
                         join t in DC.Stopoint on k.Tnx equals t.Tnx into t1
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         where (SqlMethods.DateDiffDay(k.Stime, DateTime.Parse(OutDay)) == 0)
                         && (ka.Amx == Amx)
                         && (k.Seller == Account || Account == "")
                         from o2 in o1.DefaultIfEmpty()
                         from t2 in t1.DefaultIfEmpty()
                         select new
                         {
                             Knx = k.Knx,
                             Kclerk = k.Seller,
                             Atime = k.Annul.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             GFare = f.Fare,
                             DiscntType = l.Discnt,
                             Discnt = f.Discnt,
                             Rate = f.Rate,
                             OFare = o2 == null ? 0 : o2.Fare,
                             TFare = t2 == null ? 0 : t2.Fare,
                             BookFee = v.BookFee,
                             BunkFee = f.BunkFee,
                             WharfFee = wf.Wharfee,
                             WharfAir = wf.Extras,
                             StopFee = o2 == null ? 0 : o2.Stopay,
                             StopAir = o2 == null ? 0 : o2.Extras
                         };

            List<DaySelloutClass> BookList = new List<DaySelloutClass>();
            foreach (var r in ReBook)
            {
                DaySelloutClass ttt = new DaySelloutClass
                {
                    SClerk = r.Kclerk,
                    BookNumOut = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) == 0) ?
                                (BookList.Where(y => y.BookNumOut == r.Knx).Count() == 0 ? r.Knx : 0) : 0,
                    BookNumRtn = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) != 0) ?
                                (BookList.Where(y => y.BookNumRtn == r.Knx).Count() == 0 ? r.Knx : 0) : 0,
                    BookCount = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) == 0) ? 1 : 0,
                    BookFee = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) == 0) ?
                                doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                    r.OFare, r.TFare, r.StopFee, r.StopAir) : 0,
                    BookPlus = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) == 0) ? r.BookFee : 0,
                    BookRtnCount = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) != 0) ? 1 : 0,
                    //订票的全退未处理！！！默认false
                    BookRtnFee = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) != 0) ?
                                    doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                        r.OFare, r.TFare, r.StopFee, r.StopAir, false, r.Rate) + r.BookFee : 0
                };
                BookList.Add(ttt);
            }
            List<string> BookClerkList = new List<string>();
            foreach (var r in ReBook)
            {
                if (!BookClerkList.Contains(r.Kclerk))
                    BookClerkList.Add(r.Kclerk);
            }

            foreach (var c in BookClerkList)
            {
                if (ResultList.Where(y => y.SClerk == c).Count() == 0)
                {
                    //新增
                    DaySelloutClass ccc = new DaySelloutClass
                    {
                        SClerk = c,
                        BookNumOut = BookList.Where(y => y.SClerk == c && y.BookNumOut > 0).Count(),
                        BookNumRtn = BookList.Where(y => y.SClerk == c && y.BookNumRtn > 0).Count(),
                        BookCount = BookList.Where(y => y.SClerk == c).Sum(x => x.BookCount),
                        BookFee = BookList.Where(y => y.SClerk == c).Sum(x => x.BookFee),
                        BookPlus = BookList.Where(y => y.SClerk == c).Sum(x => x.BookPlus),
                        BookRtnCount = BookList.Where(y => y.SClerk == c).Sum(x => x.BookRtnCount),
                        BookRtnFee = BookList.Where(y => y.SClerk == c).Sum(x => x.BookRtnFee),
                        BookOut = BookList.Where(y => y.SClerk == c).Sum(x => x.BookCount).ToString() + "|" +
                                    BookList.Where(y => y.SClerk == c).Sum(x => x.BookFee).ToString(),
                        BookRtn = BookList.Where(y => y.SClerk == c).Sum(x => x.BookRtnCount).ToString() + "|" +
                                    BookList.Where(y => y.SClerk == c).Sum(x => x.BookRtnFee).ToString(),
                        BookNum = BookList.Where(y => y.SClerk == c && y.BookNumOut > 0).Count().ToString() + "|" +
                                    BookList.Where(y => y.SClerk == c && y.BookNumRtn > 0).Count().ToString()
                    };
                    ResultList.Add(ccc);
                }
                else
                {
                    //修改
                    DaySelloutClass uuu = ResultList.Find(y => y.SClerk == c);
                    uuu.BookNumOut = BookList.Where(y => y.SClerk == c && y.BookNumOut > 0).Count();
                    uuu.BookNumRtn = BookList.Where(y => y.SClerk == c && y.BookNumRtn > 0).Count();
                    uuu.BookCount = BookList.Where(y => y.SClerk == c).Sum(x => x.BookCount);
                    uuu.BookFee = BookList.Where(y => y.SClerk == c).Sum(x => x.BookFee);
                    uuu.BookPlus = BookList.Where(y => y.SClerk == c).Sum(x => x.BookPlus);
                    uuu.BookRtnCount = BookList.Where(y => y.SClerk == c).Sum(x => x.BookRtnCount);
                    uuu.BookRtnFee = BookList.Where(y => y.SClerk == c).Sum(x => x.BookRtnFee);
                    uuu.BookOut = BookList.Where(y => y.SClerk == c).Sum(x => x.BookCount).ToString() + "|" +
                                BookList.Where(y => y.SClerk == c).Sum(x => x.BookFee).ToString();
                    uuu.BookRtn = BookList.Where(y => y.SClerk == c).Sum(x => x.BookRtnCount).ToString() + "|" +
                                BookList.Where(y => y.SClerk == c).Sum(x => x.BookRtnFee).ToString();
                    uuu.BookNum = BookList.Where(y => y.SClerk == c && y.BookNumOut > 0).Count().ToString() + "|" +
                                    BookList.Where(y => y.SClerk == c && y.BookNumRtn > 0).Count().ToString();
                }
            }
            //显示帐户的姓名
            foreach (var r in ResultList)
            {
                r.SClerk = Comn.AClerkDisplay(r.SClerk);
            }
            DaySelloutClass zzz = new DaySelloutClass
            {
                SClerk = "合计",
                OutCount = 0,
                OutFee = 0,
                Out = "",
                RtnCount = 0,
                RtnFee = 0,
                Rtn = "",
                AnlCount = 0,
                BookCount = 0,
                BookFee = 0,
                BookOut = "",
                BookPlus = 0,
                BookRtnCount = 0,
                BookRtnFee = 0,
                BookRtn = "",
                BookNumOut = 0,
                BookNumRtn = 0,
                BookNum = "",
                FitOutCount = 0,
                FitOutFee = 0,
                FitOut = "",
                FitRtnCount = 0,
                FitRtnFee = 0,
                FitRtn = "",
                FitAnlCount = 0
            };

            foreach(var z in ResultList)
            {
                zzz.OutCount += int.Parse(z.Out.Split('|')[0]);
                zzz.OutFee += float.Parse(z.Out.Split('|')[1]);
                zzz.RtnCount += int.Parse(z.Rtn.Split('|')[0]);
                zzz.RtnFee += float.Parse(z.Rtn.Split('|')[1]);
                zzz.AnlCount += z.AnlCount;
                zzz.BookCount += z.BookOut != null ? int.Parse(z.BookOut.Split('|')[0]) : 0;
                zzz.BookFee += z.BookOut != null ? float.Parse(z.BookOut.Split('|')[1]) : 0;
                zzz.BookPlus += z.BookPlus;
                zzz.BookRtnCount += z.BookRtn != null ? int.Parse(z.BookRtn.Split('|')[0]) : 0;
                zzz.BookRtnFee += z.BookRtn != null ? float.Parse(z.BookRtn.Split('|')[1]) : 0;
                zzz.BookNumOut += z.BookNum != null ? int.Parse(z.BookNum.Split('|')[0]) : 0;
                zzz.BookNumRtn += z.BookNum != null ? int.Parse(z.BookNum.Split('|')[1]) : 0;
                zzz.FitOutCount += int.Parse(z.FitOut.Split('|')[0]);
                zzz.FitOutFee += float.Parse(z.FitOut.Split('|')[1]);
                zzz.FitRtnCount += int.Parse(z.FitRtn.Split('|')[0]);
                zzz.FitRtnFee += float.Parse(z.FitRtn.Split('|')[1]);
                zzz.FitAnlCount += z.FitAnlCount;
            }

            zzz.Out = zzz.OutCount + "|" + zzz.OutFee;
            zzz.Rtn = zzz.RtnCount + "|" + zzz.RtnFee;
            zzz.BookOut = zzz.BookCount + "|" + zzz.BookFee;
            zzz.BookRtn = zzz.BookRtnCount + "|" + zzz.BookRtnFee;
            zzz.BookNum = zzz.BookNumOut + "|" + zzz.BookNumRtn;
            zzz.FitOut = zzz.FitOutCount + "|" + zzz.FitOutFee;
            zzz.FitRtn = zzz.FitRtnCount + "|" + zzz.FitRtnFee;

            ResultList.Add(zzz);
            return ResultList;
        }

        public List<DayRolloutClass> GetDayRollout(short Amx, string OutDay, string Account)
        {
            var Result = from r in DC.Rollout
                         join a in DC.Agent on r.Amx equals a.Amx
                         join p in DC.Vpage on r.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join f in DC.Mfare on r.Mnx equals f.Pnx
                         join t in DC.Motype on f.Tmx equals t.Tmx
                         where (a.Amx == Amx)
                         && (r.Seller == Account || Account == "")
                         && (SqlMethods.DateDiffDay(r.Stime, DateTime.Parse(OutDay)) == 0)
                         select new
                         {
                             SAmx = a.Amx,
                             SAgent = a.Nym,
                             Sclerk = r.Seller,
                             Stime = r.Stime,
                             Vabel = m.Vabel + p.Pos.ToString().PadLeft(3, '0'),
                             Rtime = r.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Checker = r.Checker == null ? "" : r.Checker,
                             MType = t.Tnym,
                             Rate = f.Rate,
                             Fare = f.Fare,
                             Plus = r.Plus,
                             Plate = r.Plate,
                             Refull = r.Refull.GetValueOrDefault(false),
                             RollFee = f.RollFee
                         };

            List<DayRolloutClass> TempList = new List<DayRolloutClass>();
            foreach (var r in Result)
            {
                DayRolloutClass ttt = new DayRolloutClass
                {
                    SClerk = r.Sclerk,
                    OneVabel = r.Vabel,
                    OutCount = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                                1 : 0,
                    OutFee = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                              doRollout(r.Fare, r.Plus, r.RollFee) : 0,
                    RtnCount = (r.Checker != "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    RtnFee = (r.Checker != "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                              doRollref(r.Fare, r.Plus, r.RollFee, r.Refull, r.Rate) : 0,
                    AnlCount = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0
                };
                TempList.Add(ttt);
            }
            List<string> ClerkList = new List<string>();
            foreach (var r in TempList)
            {
                if (!ClerkList.Contains(r.SClerk))
                    ClerkList.Add(r.SClerk);
            }
            List<DayRolloutClass> ResultList = new List<DayRolloutClass>();
            foreach (var c in ClerkList)
            {
                if (ResultList.Where(y => y.SClerk == c).Count() == 0)
                {
                    DayRolloutClass ccc = new DayRolloutClass
                    {
                        SClerk = c,
                        OutCount = TempList.Where(y => y.SClerk == c).Sum(x => x.OutCount) +
                                    TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount),
                        OutFee = TempList.Where(y => y.SClerk == c).Sum(x => x.OutFee) +
                                    TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee),
                        RtnCount = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount),
                        RtnFee = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee),
                        OneVabel = TempList.Where(y => y.SClerk == c).Min(z => z.OneVabel),
                        EndVabel = TempList.Where(y => y.SClerk == c).Max(z => z.OneVabel),
                        AnlCount = TempList.Where(y => y.SClerk == c).Sum(x => x.AnlCount),
                        Out = (TempList.Where(y => y.SClerk == c).Sum(x => x.OutCount) +
                                TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount)).ToString() + "|" +
                                (TempList.Where(y => y.SClerk == c).Sum(x => x.OutFee) +
                                TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee)).ToString(),
                        Rtn = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount).ToString() + "|" +
                               TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee).ToString()
                    };
                    ResultList.Add(ccc);
                }
            }
            //显示帐户的姓名
            foreach (var r in ResultList)
            {
                r.SClerk = Comn.AClerkDisplay(r.SClerk);
            }
            DayRolloutClass zzz = new DayRolloutClass
            {
                SClerk = "合计",
                OutCount = 0,
                OutFee = 0,
                Out = "",
                RtnCount = 0,
                RtnFee = 0,
                Rtn = "",
                AnlCount = 0,
            };

            foreach (var z in ResultList)
            {
                zzz.OutCount += int.Parse(z.Out.Split('|')[0]);
                zzz.OutFee += float.Parse(z.Out.Split('|')[1]);
                zzz.RtnCount += int.Parse(z.Rtn.Split('|')[0]);
                zzz.RtnFee += float.Parse(z.Rtn.Split('|')[1]);
                zzz.AnlCount += z.AnlCount;
            }
            zzz.Out = zzz.OutCount + "|" + zzz.OutFee;
            zzz.Rtn = zzz.RtnCount + "|" + zzz.RtnFee;
            ResultList.Add(zzz);

            return ResultList;
        }

        public List<DayLuggoutClass> GetDayLuggout(short Amx, string OutDay, string Account)
        {
            var Result = from g in DC.Luggout
                         join a in DC.Agent on g.Amx equals a.Amx
                         join p in DC.Vpage on g.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join wy in DC.Wharfiny on g.Ynx equals wy.Ynx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join v in DC.Voyage on wf.Vnx equals v.Vnx
                         where (a.Amx == Amx)
                         && (g.Seller == Account || Account == "")
                         && (SqlMethods.DateDiffDay(g.Stime, DateTime.Parse(OutDay)) == 0)
                         select new
                         {
                             Rate = v.Rate,
                             SAmx = a.Amx,
                             SAgent = a.Nym,
                             Sclerk = g.Seller,
                             Stime = g.Stime,
                             Vabel = m.Vabel + p.Pos.ToString().PadLeft(3, '0'),
                             Rtime = g.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Checker = g.Checker == null ? "" : g.Checker,
                             Fare = wy.Fare,
                             Heft = g.Heft,
                             Refull = g.Refull.GetValueOrDefault(false),
                             LuggFee = wy.LuggFee
                         };
            List<DayLuggoutClass> TempList = new List<DayLuggoutClass>();
            foreach (var r in Result)
            {
                DayLuggoutClass ttt = new DayLuggoutClass
                {
                    SClerk = r.Sclerk,
                    OneVabel = r.Vabel,
                    OutCount = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                                1 : 0,
                    OutFee = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                              doLuggout(r.Fare, r.Heft, r.LuggFee) : 0,
                    RtnCount = (r.Checker != "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    RtnFee = (r.Checker != "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                              doLuggref(r.Fare, r.Heft, r.LuggFee, r.Refull , r.Rate) : 0,
                    AnlCount = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0
                };
                TempList.Add(ttt);
            }
            List<string> ClerkList = new List<string>();
            foreach (var r in TempList)
            {
                if (!ClerkList.Contains(r.SClerk))
                    ClerkList.Add(r.SClerk);
            } 
            List<DayLuggoutClass> ResultList = new List<DayLuggoutClass>();
            foreach (var c in ClerkList)
            {
                if (ResultList.Where(y => y.SClerk == c).Count() == 0)
                {
                    DayLuggoutClass ccc = new DayLuggoutClass
                    {
                        SClerk = c,
                        OutCount = TempList.Where(y => y.SClerk == c).Sum(x => x.OutCount) +
                                    TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount),
                        OutFee = TempList.Where(y => y.SClerk == c).Sum(x => x.OutFee) +
                                    TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee),
                        RtnCount = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount),
                        RtnFee = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee),
                        OneVabel = TempList.Where(y => y.SClerk == c).Min(z => z.OneVabel),
                        EndVabel = TempList.Where(y => y.SClerk == c).Max(z => z.OneVabel),
                        AnlCount = TempList.Where(y => y.SClerk == c).Sum(x => x.AnlCount),
                        Out = (TempList.Where(y => y.SClerk == c).Sum(x => x.OutCount) +
                                TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount)).ToString() + "|" +
                                (TempList.Where(y => y.SClerk == c).Sum(x => x.OutFee) +
                                TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee)).ToString(),
                        Rtn = TempList.Where(y => y.SClerk == c).Sum(x => x.RtnCount).ToString() + "|" +
                               TempList.Where(y => y.SClerk == c).Sum(x => x.RtnFee).ToString()
                    };
                    ResultList.Add(ccc);
                }
            }
            //显示帐户的姓名
            foreach (var r in ResultList)
            {
                r.SClerk = Comn.AClerkDisplay(r.SClerk);
            }
            DayLuggoutClass zzz = new DayLuggoutClass
            {
                SClerk = "合计",
                OutCount = 0,
                OutFee = 0,
                Out = "",
                RtnCount = 0,
                RtnFee = 0,
                Rtn = "",
                AnlCount = 0,
            };

            foreach (var z in ResultList)
            {
                zzz.OutCount += int.Parse(z.Out.Split('|')[0]);
                zzz.OutFee += float.Parse(z.Out.Split('|')[1]);
                zzz.RtnCount += int.Parse(z.Rtn.Split('|')[0]);
                zzz.RtnFee += float.Parse(z.Rtn.Split('|')[1]);
                zzz.AnlCount += z.AnlCount;
            }
            zzz.Out = zzz.OutCount + "|" + zzz.OutFee;
            zzz.Rtn = zzz.RtnCount + "|" + zzz.RtnFee;
            ResultList.Add(zzz);

            return ResultList;
        }

        public List<MonSelloutClass> GetMonSellout(short Amx, string DayFrom, string DayTo)
        {
            var Result = from s in DC.Sellout
                         join l in DC.Lockup on s.Lnx equals l.Lnx
                         join o in DC.Stoplace on s.Onx equals o.Onx into o1
                         join t in DC.Stopoint on s.Tnx equals t.Tnx into t1
                         join sa in DC.Agent on s.Smx equals sa.Amx
                         join ra in DC.Agent on s.Rmx equals ra.Amx into ra1
                         join p in DC.Vpage on s.Pnx equals p.Pnx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         where (s.Stime > DateTime.Parse(DayFrom))
                         && (s.Stime < DateTime.Parse(DayTo).AddDays(1))
                         && (sa.Amx == Amx)
                         from o2 in o1.DefaultIfEmpty()
                         from t2 in t1.DefaultIfEmpty()
                         from ra2 in ra1.DefaultIfEmpty()
                         select new
                         {
                             OutDate = s.Stime.ToShortDateString(),
                             Knx = l.Knx.GetValueOrDefault(0),
                             DiscntType = l.Discnt,
                             GFare = f.Fare,
                             Discnt = f.Discnt,
                             Rate = f.Rate,
                             OFare = o2 == null ? 0 : o2.Fare,
                             TFare = t2 == null ? 0 : t2.Fare,
                             SAgent = sa.Nym,
                             RAgent = ra2 == null ? "" : ra2.Nym,
                             Rclerk = s.Rclerk == null ? "" : s.Rclerk,
                             Rtime = s.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             BookFee = s.Bookfee.GetValueOrDefault(false),
                             Refull = s.Refull.GetValueOrDefault(false),
                             BunkFee = f.BunkFee,
                             WharfFee = wf.Wharfee,
                             WharfAir = wf.Extras,
                             StopFee = o2 == null ? 0 : o2.Stopay,
                             StopAir = o2 == null ? 0 : o2.Extras
                         };

            List<MonSelloutClass> TempList = new List<MonSelloutClass>();
            foreach (var r in Result)
            {
                MonSelloutClass ttt = new MonSelloutClass
                {
                    OutDate = r.OutDate,
                    OutCount = (r.Knx == 0 && r.RAgent == "" && r.Rclerk == "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                                1 : 0,
                    OutFee = (r.Knx == 0 && r.RAgent == "" && r.Rclerk == "" &&
                              SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                              doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                r.OFare, r.TFare, r.StopFee, r.StopAir) : 0,
                    FitOutCount = (r.Knx != 0 && r.RAgent == "" && r.Rclerk == "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                                1 : 0,
                    FitOutFee = (r.Knx != 0 && r.RAgent == "" && r.Rclerk == "" &&
                              SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                              doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                r.OFare, r.TFare, r.StopFee, r.StopAir) : 0,
                    RtnCount = (r.Knx == 0 && r.RAgent != "" && r.Rclerk != "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    RtnFee = (r.Knx == 0 && r.RAgent != "" && r.Rclerk != "" &&
                              SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                              doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                    r.OFare, r.TFare, r.StopFee, r.StopAir, r.Refull, r.Rate) : 0,
                    FitRtnCount = (r.Knx != 0 && r.RAgent != "" && r.Rclerk != "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    FitRtnFee = (r.Knx != 0 && r.RAgent != "" && r.Rclerk != "" &&
                              SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                              doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                    r.OFare, r.TFare, r.StopFee, r.StopAir, r.Refull, r.Rate) : 0,
                    AnlCount = (r.Knx == 0 && r.RAgent == "" && r.Rclerk == "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    FitAnlCount = (r.Knx != 0 && r.RAgent == "" && r.Rclerk == "" &&
                                SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0
                };
                TempList.Add(ttt);
            }

            List<string> SellDateList = new List<string>();
            foreach (var d in Result)
            {
                if (!SellDateList.Contains(d.OutDate))
                    SellDateList.Add(d.OutDate);
            }

            List<MonSelloutClass> ResultList = new List<MonSelloutClass>();
            foreach (var c in SellDateList)
            {
                MonSelloutClass ccc = new MonSelloutClass
                {
                    OutDate = c,
                    OutCount = TempList.Where(y => y.OutDate == c).Sum(x => x.OutCount) +
                                TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount),
                    OutFee = TempList.Where(y => y.OutDate == c).Sum(x => x.OutFee) +
                                TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee),
                    RtnCount = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount),
                    RtnFee = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee),
                    FitOutCount = TempList.Where(y => y.OutDate == c).Sum(x => x.FitOutCount) +
                                    TempList.Where(y => y.OutDate == c).Sum(x => x.FitRtnCount),
                    FitOutFee = TempList.Where(y => y.OutDate == c).Sum(x => x.FitOutFee) +
                                    TempList.Where(y => y.OutDate == c).Sum(x => x.FitRtnFee),
                    FitRtnCount = TempList.Where(y => y.OutDate == c).Sum(x => x.FitRtnCount),
                    FitRtnFee = TempList.Where(y => y.OutDate == c).Sum(x => x.FitRtnFee),
                    AnlCount = TempList.Where(y => y.OutDate == c).Sum(x => x.AnlCount),
                    FitAnlCount = TempList.Where(y => y.OutDate == c).Sum(x => x.FitAnlCount),
                    Out = (TempList.Where(y => y.OutDate == c).Sum(x => x.OutCount) +
                            TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount)).ToString() + "|" +
                            (TempList.Where(y => y.OutDate == c).Sum(x => x.OutFee) +
                            TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee)).ToString(),
                    Rtn = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount).ToString() + "|" +
                           TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee).ToString(),
                    FitOut = (TempList.Where(y => y.OutDate == c).Sum(x => x.FitOutCount) +
                                TempList.Where(y => y.OutDate == c).Sum(x => x.FitRtnCount)).ToString() + "|" +
                                (TempList.Where(y => y.OutDate == c).Sum(x => x.FitOutFee) +
                                TempList.Where(y => y.OutDate == c).Sum(x => x.FitRtnFee)).ToString(),
                    FitRtn = (TempList.Where(y => y.OutDate == c).Sum(x => x.FitRtnCount)).ToString() + "|" +
                                (TempList.Where(y => y.OutDate == c).Sum(x => x.FitRtnFee)).ToString()
                };
                ResultList.Add(ccc);
            }
            //处理订票
            var ReBook = from l in DC.Lockup
                         join k in DC.Bookeep on l.Knx equals k.Knx
                         join ka in DC.Agent on k.Amx equals ka.Amx
                         join o in DC.Stoplace on k.Onx equals o.Onx into o1
                         join t in DC.Stopoint on k.Tnx equals t.Tnx into t1
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         where (k.Stime > DateTime.Parse(DayFrom))
                         && (k.Stime < DateTime.Parse(DayTo).AddDays(1))
                         && (ka.Amx == Amx)
                         from o2 in o1.DefaultIfEmpty()
                         from t2 in t1.DefaultIfEmpty()
                         select new
                         {

                             OutDate = k.Stime.ToShortDateString(),
                             Knx = k.Knx,
                             Kclerk = k.Seller,
                             Atime = k.Annul.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             GFare = f.Fare,
                             DiscntType = l.Discnt,
                             Discnt = f.Discnt,
                             Rate = f.Rate,
                             OFare = o2 == null ? 0 : o2.Fare,
                             TFare = t2 == null ? 0 : t2.Fare,
                             BookFee = v.BookFee,
                             BunkFee = f.BunkFee,
                             WharfFee = wf.Wharfee,
                             WharfAir = wf.Extras,
                             StopFee = o2 == null ? 0 : o2.Stopay,
                             StopAir = o2 == null ? 0 : o2.Extras
                         };

            List<MonSelloutClass> BookList = new List<MonSelloutClass>();
            foreach (var r in ReBook)
            {
                MonSelloutClass ttt = new MonSelloutClass
                {
                    OutDate = r.OutDate,
                    BookNumOut = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) == 0) ?
                                (BookList.Where(y => y.BookNumOut == r.Knx).Count() == 0 ? r.Knx : 0) : 0,
                    BookNumRtn = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) != 0) ?
                                (BookList.Where(y => y.BookNumRtn == r.Knx).Count() == 0 ? r.Knx : 0) : 0,
                    BookCount = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) == 0) ? 1 : 0,
                    BookFee = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) == 0) ?
                                doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                    r.OFare, r.TFare, r.StopFee, r.StopAir) : 0,
                    BookPlus = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) == 0) ? r.BookFee : 0,
                    BookRtnCount = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) != 0) ? 1 : 0,
                    //订票的全退未处理！！！默认false
                    BookRtnFee = (SqlMethods.DateDiffDay(r.Atime, DateTime.Parse("1900-1-1")) != 0) ?
                                    doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                        r.OFare, r.TFare, r.StopFee, r.StopAir, false, r.Rate) + r.BookFee : 0
                };
                BookList.Add(ttt);
            }
            List<string> BookDateList = new List<string>();
            foreach (var r in ReBook)
            {
                if (!BookDateList.Contains(r.OutDate))
                    BookDateList.Add(r.OutDate);
            }

            foreach (var c in BookDateList)
            {
                if (ResultList.Where(y => y.OutDate == c).Count() == 0)
                {
                    //新增
                    MonSelloutClass ccc = new MonSelloutClass
                    {
                        OutDate = c,
                        BookNumOut = BookList.Where(y => y.OutDate == c && y.BookNumOut > 0).Count(),
                        BookNumRtn = BookList.Where(y => y.OutDate == c && y.BookNumRtn > 0).Count(),
                        BookCount = BookList.Where(y => y.OutDate == c).Sum(x => x.BookCount),
                        BookFee = BookList.Where(y => y.OutDate == c).Sum(x => x.BookFee),
                        BookPlus = BookList.Where(y => y.OutDate == c).Sum(x => x.BookPlus),
                        BookRtnCount = BookList.Where(y => y.OutDate == c).Sum(x => x.BookRtnCount),
                        BookRtnFee = BookList.Where(y => y.OutDate == c).Sum(x => x.BookRtnFee),
                        BookOut = BookList.Where(y => y.OutDate == c).Sum(x => x.BookCount).ToString() + "|" +
                                    BookList.Where(y => y.OutDate == c).Sum(x => x.BookFee).ToString(),
                        BookRtn = BookList.Where(y => y.OutDate == c).Sum(x => x.BookRtnCount).ToString() + "|" +
                                    BookList.Where(y => y.OutDate == c).Sum(x => x.BookRtnFee).ToString(),
                        BookNum = BookList.Where(y => y.OutDate == c && y.BookNumOut > 0).Count().ToString() + "|" +
                                    BookList.Where(y => y.OutDate == c && y.BookNumRtn > 0).Count().ToString()
                    };
                    ResultList.Add(ccc);
                }
                else
                {
                    //修改
                    MonSelloutClass uuu = ResultList.Find(y => y.OutDate == c);
                    uuu.BookNumOut = BookList.Where(y => y.OutDate == c && y.BookNumOut > 0).Count();
                    uuu.BookNumRtn = BookList.Where(y => y.OutDate == c && y.BookNumRtn > 0).Count();
                    uuu.BookCount = BookList.Where(y => y.OutDate == c).Sum(x => x.BookCount);
                    uuu.BookFee = BookList.Where(y => y.OutDate == c).Sum(x => x.BookFee);
                    uuu.BookPlus = BookList.Where(y => y.OutDate == c).Sum(x => x.BookPlus);
                    uuu.BookRtnCount = BookList.Where(y => y.OutDate == c).Sum(x => x.BookRtnCount);
                    uuu.BookRtnFee = BookList.Where(y => y.OutDate == c).Sum(x => x.BookRtnFee);
                    uuu.BookOut = BookList.Where(y => y.OutDate == c).Sum(x => x.BookCount).ToString() + "|" +
                                BookList.Where(y => y.OutDate == c).Sum(x => x.BookFee).ToString();
                    uuu.BookRtn = BookList.Where(y => y.OutDate == c).Sum(x => x.BookRtnCount).ToString() + "|" +
                                BookList.Where(y => y.OutDate == c).Sum(x => x.BookRtnFee).ToString();
                    uuu.BookNum = BookList.Where(y => y.OutDate == c && y.BookNumOut > 0).Count().ToString() + "|" +
                                    BookList.Where(y => y.OutDate == c && y.BookNumRtn > 0).Count().ToString();
                }
            }
            MonSelloutClass zzz = new MonSelloutClass
            {
                OutDate = "合计",
                OutCount = 0,
                OutFee = 0,
                Out = "",
                RtnCount = 0,
                RtnFee = 0,
                Rtn = "",
                AnlCount = 0,
                BookCount = 0,
                BookFee = 0,
                BookOut = "",
                BookPlus = 0,
                BookRtnCount = 0,
                BookRtnFee = 0,
                BookRtn = "",
                BookNumOut = 0,
                BookNumRtn = 0,
                BookNum = "",
                FitOutCount = 0,
                FitOutFee = 0,
                FitOut = "",
                FitRtnCount = 0,
                FitRtnFee = 0,
                FitRtn = "",
                FitAnlCount = 0
            };

            foreach (var z in ResultList)
            {
                zzz.OutCount += int.Parse(z.Out.Split('|')[0]);
                zzz.OutFee += float.Parse(z.Out.Split('|')[1]);
                zzz.RtnCount += int.Parse(z.Rtn.Split('|')[0]);
                zzz.RtnFee += float.Parse(z.Rtn.Split('|')[1]);
                zzz.AnlCount += z.AnlCount;
                zzz.BookCount += z.BookOut != null ? int.Parse(z.BookOut.Split('|')[0]) : 0;
                zzz.BookFee += z.BookOut != null ? float.Parse(z.BookOut.Split('|')[1]) : 0;
                zzz.BookPlus += z.BookPlus;
                zzz.BookRtnCount += z.BookRtn != null ? int.Parse(z.BookRtn.Split('|')[0]) : 0;
                zzz.BookRtnFee += z.BookRtn != null ? float.Parse(z.BookRtn.Split('|')[1]) : 0;
                zzz.BookNumOut += z.BookNum != null ? int.Parse(z.BookNum.Split('|')[0]) : 0;
                zzz.BookNumRtn += z.BookNum != null ? int.Parse(z.BookNum.Split('|')[1]) : 0;
                zzz.FitOutCount += int.Parse(z.FitOut.Split('|')[0]);
                zzz.FitOutFee += float.Parse(z.FitOut.Split('|')[1]);
                zzz.FitRtnCount += int.Parse(z.FitRtn.Split('|')[0]);
                zzz.FitRtnFee += float.Parse(z.FitRtn.Split('|')[1]);
                zzz.FitAnlCount += z.FitAnlCount;
            }

            zzz.Out = zzz.OutCount + "|" + zzz.OutFee;
            zzz.Rtn = zzz.RtnCount + "|" + zzz.RtnFee;
            zzz.BookOut = zzz.BookCount + "|" + zzz.BookFee;
            zzz.BookRtn = zzz.BookRtnCount + "|" + zzz.BookRtnFee;
            zzz.BookNum = zzz.BookNumOut + "|" + zzz.BookNumRtn;
            zzz.FitOut = zzz.FitOutCount + "|" + zzz.FitOutFee;
            zzz.FitRtn = zzz.FitRtnCount + "|" + zzz.FitRtnFee;

            ResultList.Add(zzz);
            return ResultList;
        }

        public List<MonRolloutClass> GetMonRollout(short Amx, string DayFrom, string DayTo)
        {
            var Result = from r in DC.Rollout
                         join a in DC.Agent on r.Amx equals a.Amx
                         join p in DC.Vpage on r.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join f in DC.Mfare on r.Mnx equals f.Pnx
                         join t in DC.Motype on f.Tmx equals t.Tmx
                         where (r.Stime > DateTime.Parse(DayFrom))
                         && (r.Stime < DateTime.Parse(DayTo).AddDays(1))
                         && (a.Amx == Amx)
                         select new
                         {
                             SAmx = a.Amx,
                             SAgent = a.Nym,
                             Sclerk = r.Seller,
                             OutDate = r.Stime.ToShortDateString(),
                             Rtime = r.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Checker = r.Checker == null ? "" : r.Checker,
                             MType = t.Tnym,
                             Rate = f.Rate,
                             Fare = f.Fare,
                             Plus = r.Plus,
                             Plate = r.Plate,
                             Refull = r.Refull.GetValueOrDefault(false),
                             RollFee = f.RollFee
                         };

            List<MonRolloutClass> TempList = new List<MonRolloutClass>();
            foreach (var r in Result)
            {
                MonRolloutClass ttt = new MonRolloutClass
                {
                    OutDate = r.OutDate,
                    OutCount = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                                1 : 0,
                    OutFee = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                              doRollout(r.Fare, r.Plus, r.RollFee) : 0,
                    RtnCount = (r.Checker != "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    RtnFee = (r.Checker != "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                              doRollref(r.Fare, r.Plus, r.RollFee, r.Refull, r.Rate) : 0,
                    AnlCount = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0
                };
                TempList.Add(ttt);
            }
            List<string> RollDateList = new List<string>();
            foreach (var d in Result)
            {
                if (!RollDateList.Contains(d.OutDate))
                    RollDateList.Add(d.OutDate);
            }

            List<MonRolloutClass> ResultList = new List<MonRolloutClass>();
            foreach (var c in RollDateList)
            {
                MonRolloutClass ccc = new MonRolloutClass
                {
                    OutDate = c,
                    OutCount = TempList.Where(y => y.OutDate == c).Sum(x => x.OutCount) +
                                TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount),
                    OutFee = TempList.Where(y => y.OutDate == c).Sum(x => x.OutFee) +
                                TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee),
                    RtnCount = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount),
                    RtnFee = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee),
                    AnlCount = TempList.Where(y => y.OutDate == c).Sum(x => x.AnlCount),
                    Out = (TempList.Where(y => y.OutDate == c).Sum(x => x.OutCount) +
                            TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount)).ToString() + "|" +
                            (TempList.Where(y => y.OutDate == c).Sum(x => x.OutFee) +
                            TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee)).ToString(),
                    Rtn = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount).ToString() + "|" +
                           TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee).ToString()
                };
                ResultList.Add(ccc);
            }
            MonRolloutClass zzz = new MonRolloutClass
            {
                OutDate = "合计",
                OutCount = 0,
                OutFee = 0,
                Out = "",
                RtnCount = 0,
                RtnFee = 0,
                Rtn = "",
                AnlCount = 0,
            };

            foreach (var z in ResultList)
            {
                zzz.OutCount += int.Parse(z.Out.Split('|')[0]);
                zzz.OutFee += float.Parse(z.Out.Split('|')[1]);
                zzz.RtnCount += int.Parse(z.Rtn.Split('|')[0]);
                zzz.RtnFee += float.Parse(z.Rtn.Split('|')[1]);
                zzz.AnlCount += z.AnlCount;
            }
            zzz.Out = zzz.OutCount + "|" + zzz.OutFee;
            zzz.Rtn = zzz.RtnCount + "|" + zzz.RtnFee;
            ResultList.Add(zzz);

            return ResultList;
        }

        public List<MonLuggoutClass> GetMonLuggout(short Amx, string DayFrom, string DayTo)
        {
            var Result = from g in DC.Luggout
                         join a in DC.Agent on g.Amx equals a.Amx
                         join p in DC.Vpage on g.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join wy in DC.Wharfiny on g.Ynx equals wy.Ynx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join v in DC.Voyage on wf.Vnx equals v.Vnx
                         where (g.Stime > DateTime.Parse(DayFrom))
                         && (g.Stime < DateTime.Parse(DayTo).AddDays(1))
                         && (a.Amx == Amx)
                         select new
                         {
                             Rate = v.Rate,
                             SAmx = a.Amx,
                             SAgent = a.Nym,
                             Sclerk = g.Seller,
                             OutDate = g.Stime.ToShortDateString(),
                             Rtime = g.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Checker = g.Checker == null ? "" : g.Checker,
                             Fare = wy.Fare,
                             Heft = g.Heft,
                             Refull = g.Refull.GetValueOrDefault(false),
                             LuggFee = wy.LuggFee
                         };
            List<MonLuggoutClass> TempList = new List<MonLuggoutClass>();
            foreach (var r in Result)
            {
                MonLuggoutClass ttt = new MonLuggoutClass
                {
                    OutDate = r.OutDate,
                    OutCount = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                                1 : 0,
                    OutFee = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) == 0) ?
                              doLuggout(r.Fare, r.Heft, r.LuggFee) : 0,
                    RtnCount = (r.Checker != "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0,
                    RtnFee = (r.Checker != "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                              doLuggref(r.Fare, r.Heft, r.LuggFee, r.Refull, r.Rate) : 0,
                    AnlCount = (r.Checker == "" && SqlMethods.DateDiffDay(r.Rtime, DateTime.Parse("1900-1-1")) != 0) ?
                                1 : 0
                };
                TempList.Add(ttt);
            }
            List<string> LuggDateList = new List<string>();
            foreach (var d in Result)
            {
                if (!LuggDateList.Contains(d.OutDate))
                    LuggDateList.Add(d.OutDate);
            }
            List<MonLuggoutClass> ResultList = new List<MonLuggoutClass>();
            foreach (var c in LuggDateList)
            {
                MonLuggoutClass ccc = new MonLuggoutClass
                {
                    OutDate = c,
                    OutCount = TempList.Where(y => y.OutDate == c).Sum(x => x.OutCount) +
                                TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount),
                    OutFee = TempList.Where(y => y.OutDate == c).Sum(x => x.OutFee) +
                                TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee),
                    RtnCount = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount),
                    RtnFee = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee),
                    AnlCount = TempList.Where(y => y.OutDate == c).Sum(x => x.AnlCount),
                    Out = (TempList.Where(y => y.OutDate == c).Sum(x => x.OutCount) +
                            TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount)).ToString() + "|" +
                            (TempList.Where(y => y.OutDate == c).Sum(x => x.OutFee) +
                            TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee)).ToString(),
                    Rtn = TempList.Where(y => y.OutDate == c).Sum(x => x.RtnCount).ToString() + "|" +
                           TempList.Where(y => y.OutDate == c).Sum(x => x.RtnFee).ToString()
                };
                ResultList.Add(ccc);
            }
            MonLuggoutClass zzz = new MonLuggoutClass
            {
                OutDate = "合计",
                OutCount = 0,
                OutFee = 0,
                Out = "",
                RtnCount = 0,
                RtnFee = 0,
                Rtn = "",
                AnlCount = 0,
            };

            foreach (var z in ResultList)
            {
                zzz.OutCount += int.Parse(z.Out.Split('|')[0]);
                zzz.OutFee += float.Parse(z.Out.Split('|')[1]);
                zzz.RtnCount += int.Parse(z.Rtn.Split('|')[0]);
                zzz.RtnFee += float.Parse(z.Rtn.Split('|')[1]);
                zzz.AnlCount += z.AnlCount;
            }
            zzz.Out = zzz.OutCount + "|" + zzz.OutFee;
            zzz.Rtn = zzz.RtnCount + "|" + zzz.RtnFee;
            ResultList.Add(zzz);

            return ResultList;
        }

        public List<MonSellrefClass> GetMonSellref(short Amx, string DayFrom, string DayTo)
        {
            var Result = from s in DC.Sellout
                         join l in DC.Lockup on s.Lnx equals l.Lnx
                         join o in DC.Stoplace on s.Onx equals o.Onx into o1
                         join t in DC.Stopoint on s.Tnx equals t.Tnx into t1
                         join sa in DC.Agent on s.Smx equals sa.Amx
                         join ra in DC.Agent on s.Rmx equals ra.Amx into ra1
                         join p in DC.Vpage on s.Pnx equals p.Pnx
                         join b in DC.Vbunk on l.Tnx equals b.Tnx
                         join c in DC.Cabin on b.Cmx equals c.Cmx
                         join g in DC.Grade on c.Gmx equals g.Gmx
                         join v in DC.Voyage on b.Vnx equals v.Vnx
                         join wf in DC.Wharfrom on v.Vnx equals wf.Vnx
                         join wy in DC.Wharfiny on wf.Fnx equals wy.Fnx
                         join f in DC.Gfare on new { wy.Ynx, g.Gmx, l.Flag } equals new { f.Ynx, f.Gmx, f.Flag }
                         from o2 in o1.DefaultIfEmpty()
                         from t2 in t1.DefaultIfEmpty()
                         from ra2 in ra1.DefaultIfEmpty()
                         where ra2 != null && s.Rclerk != null && s.Rtime != null
                         && (s.Rtime > DateTime.Parse(DayFrom))
                         && (s.Rtime < DateTime.Parse(DayTo).AddDays(1))
                         && (ra2.Amx == Amx)
                         select new
                         {
                             DiscntType = l.Discnt,
                             GFare = f.Fare,
                             Discnt = f.Discnt,
                             Rate = f.Rate,
                             OFare = o2 == null ? 0 : o2.Fare,
                             TFare = t2 == null ? 0 : t2.Fare,
                             SAmx = sa.Amx,
                             RAmx = ra2 == null ? 0 : ra2.Amx,
                             SAgent = sa.Nym,
                             RAgent = ra2 == null ? "" : ra2.Nym,
                             Rclerk = s.Rclerk == null ? "" : s.Rclerk,
                             Rtime = s.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             BookFee = s.Bookfee.GetValueOrDefault(false),
                             Refull = s.Refull.GetValueOrDefault(false),
                             BunkFee = f.BunkFee,
                             WharfFee = wf.Wharfee,
                             WharfAir = wf.Extras,
                             StopFee = o2 == null ? 0 : o2.Stopay,
                             StopAir = o2 == null ? 0 : o2.Extras
                         };

            List<MonSellrefClass> TempList = new List<MonSellrefClass>();
            List<string> RefDateList = new List<string>();
            foreach (var r in Result)
            {
                MonSellrefClass ttt = new MonSellrefClass
                {
                    RefDate = r.Rtime.ToShortDateString(),
                    LocalFullRefCount = (r.SAmx == r.RAmx && r.Refull) ? 1 : 0,
                    LocalFullRefPrice = (r.SAmx == r.RAmx && r.Refull) ? doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, 
                                        r.WharfFee, r.WharfAir, r.OFare, r.TFare, r.StopFee, r.StopAir) : 0,
                    LocalHalfRefCount = (r.SAmx == r.RAmx && !r.Refull) ? 1 : 0,
                    LocalHalfRefPrice = (r.SAmx == r.RAmx && !r.Refull) ? doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, 
                                        r.WharfFee, r.WharfAir, r.OFare, r.TFare, r.StopFee, r.StopAir, r.Refull, r.Rate) : 0,
                    LocalHalfRefFee = (r.SAmx == r.RAmx && !r.Refull) ? doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee,
                                        r.WharfFee, r.WharfAir, r.OFare, r.TFare, r.StopFee, r.StopAir) - 
                                        doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir, 
                                        r.OFare, r.TFare, r.StopFee, r.StopAir, r.Refull, r.Rate) : 0,
                    OtherFullRefCount = (r.SAmx != r.RAmx && r.Refull) ? 1 : 0,
                    OtherFullRefPrice = (r.SAmx != r.RAmx && r.Refull) ? doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee,
                                        r.WharfFee, r.WharfAir, r.OFare, r.TFare, r.StopFee, r.StopAir) : 0,
                    OtherHalfRefCount = (r.SAmx != r.RAmx && !r.Refull) ? 1 : 0,
                    OtherHalfRefPrice = (r.SAmx != r.RAmx && !r.Refull) ? doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee,
                                        r.WharfFee, r.WharfAir, r.OFare, r.TFare, r.StopFee, r.StopAir, r.Refull, r.Rate) : 0,
                    OtherHalfRefFee = (r.SAmx != r.RAmx && !r.Refull) ? doSellout(r.DiscntType, r.Discnt, r.GFare, r.BunkFee,
                                        r.WharfFee, r.WharfAir, r.OFare, r.TFare, r.StopFee, r.StopAir) -
                                        doSellref(r.DiscntType, r.Discnt, r.GFare, r.BunkFee, r.WharfFee, r.WharfAir,
                                        r.OFare, r.TFare, r.StopFee, r.StopAir, r.Refull, r.Rate) : 0
                };
                TempList.Add(ttt);
                if (!RefDateList.Contains(ttt.RefDate))
                    RefDateList.Add(ttt.RefDate);
            }

            List<MonSellrefClass> ResultList = new List<MonSellrefClass>();
            foreach (var c in RefDateList)
            {
                MonSellrefClass ccc = new MonSellrefClass
                {
                    RefDate = c,
                    LocalFullRefCount = TempList.Where(y => y.RefDate == c).Sum(x => x.LocalFullRefCount),
                    LocalFullRefPrice = TempList.Where(y => y.RefDate == c).Sum(x => x.LocalFullRefPrice),
                    LocalHalfRefCount = TempList.Where(y => y.RefDate == c).Sum(x => x.LocalHalfRefCount),
                    LocalHalfRefPrice = TempList.Where(y => y.RefDate == c).Sum(x => x.LocalHalfRefPrice),
                    LocalHalfRefFee   = TempList.Where(y => y.RefDate == c).Sum(x => x.LocalHalfRefFee),
                    OtherFullRefCount = TempList.Where(y => y.RefDate == c).Sum(x => x.OtherFullRefCount),
                    OtherFullRefPrice = TempList.Where(y => y.RefDate == c).Sum(x => x.OtherFullRefPrice),
                    OtherHalfRefCount = TempList.Where(y => y.RefDate == c).Sum(x => x.OtherHalfRefCount),
                    OtherHalfRefPrice = TempList.Where(y => y.RefDate == c).Sum(x => x.OtherHalfRefPrice),
                    OtherHalfRefFee   = TempList.Where(y => y.RefDate == c).Sum(x => x.OtherHalfRefFee),
                };
                ResultList.Add(ccc);
            }
            MonSellrefClass zzz = new MonSellrefClass
            {
                RefDate = "合计",
                LocalFullRefCount = 0,
                LocalFullRefPrice = 0,
                LocalHalfRefCount = 0,
                LocalHalfRefPrice = 0,
                LocalHalfRefFee   = 0,
                OtherFullRefCount = 0,
                OtherFullRefPrice = 0,
                OtherHalfRefCount = 0,
                OtherHalfRefPrice = 0,
                OtherHalfRefFee   = 0
            };

            foreach (var z in ResultList)
            {
                zzz.LocalFullRefCount += z.LocalFullRefCount;
                zzz.LocalFullRefPrice += z.LocalFullRefPrice;
                zzz.LocalHalfRefCount += z.LocalHalfRefCount;
                zzz.LocalHalfRefPrice += z.LocalHalfRefPrice;
                zzz.LocalHalfRefFee   += z.LocalHalfRefFee;
                zzz.OtherFullRefCount += z.OtherFullRefCount;
                zzz.OtherFullRefPrice += z.OtherFullRefPrice;
                zzz.OtherHalfRefCount += z.OtherHalfRefCount;
                zzz.OtherHalfRefPrice += z.OtherHalfRefPrice;
                zzz.OtherHalfRefFee   += z.OtherHalfRefFee;
            }
            ResultList.Add(zzz);

            return ResultList;
        }

        public List<MonLuggrefClass> GetMonLuggref(short Amx, string DayFrom, string DayTo)
        {
            var Result = from g in DC.Luggout
                         join a in DC.Agent on g.Amx equals a.Amx
                         join p in DC.Vpage on g.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join wy in DC.Wharfiny on g.Ynx equals wy.Ynx
                         join wf in DC.Wharfrom on wy.Fnx equals wf.Fnx
                         join v in DC.Voyage on wf.Vnx equals v.Vnx
                         where  g.Checker != null && g.Rtime != null
                         && (g.Rtime > DateTime.Parse(DayFrom))
                         && (g.Rtime < DateTime.Parse(DayTo).AddDays(1))
                         && (a.Amx == Amx)
                         select new
                         {
                             Rate = v.Rate,
                             SAmx = a.Amx,
                             SAgent = a.Nym,
                             Sclerk = g.Seller,
                             OutDate = g.Stime.ToShortDateString(),
                             Rtime = g.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Checker = g.Checker == null ? "" : g.Checker,
                             Fare = wy.Fare,
                             Heft = g.Heft,
                             Refull = g.Refull.GetValueOrDefault(false),
                             LuggFee = wy.LuggFee
                         };
            List<MonLuggrefClass> TempList = new List<MonLuggrefClass>();
            List<string> LuggDateList = new List<string>();
            foreach (var r in Result)
            {
                MonLuggrefClass ttt = new MonLuggrefClass
                {
                    RefDate = r.Rtime.ToShortDateString(),
                    FullRefCount = r.Refull ? 1 : 0,
                    FullRefPrice = r.Refull ? doLuggout(r.Fare, r.Heft, r.LuggFee) : 0,
                    HalfRefCount = !r.Refull ? 1 : 0,
                    HalfRefPrice = !r.Refull ? doLuggref(r.Fare, r.Heft, r.LuggFee, r.Refull, r.Rate) : 0,
                    HalfRefFee = !r.Refull ? doLuggout(r.Fare, r.Heft, r.LuggFee) - doLuggref(r.Fare, r.Heft, r.LuggFee, r.Refull, r.Rate) : 0
                };
                TempList.Add(ttt);
                if (!LuggDateList.Contains(ttt.RefDate))
                    LuggDateList.Add(ttt.RefDate);
            }

            List<MonLuggrefClass> ResultList = new List<MonLuggrefClass>();
            foreach (var c in LuggDateList)
            {
                MonLuggrefClass ccc = new MonLuggrefClass
                {
                    RefDate = c,
                    FullRefCount = TempList.Where(y => y.RefDate == c).Sum(x => x.FullRefCount),
                    FullRefPrice = TempList.Where(y => y.RefDate == c).Sum(x => x.FullRefPrice),
                    HalfRefCount = TempList.Where(y => y.RefDate == c).Sum(x => x.HalfRefCount),
                    HalfRefPrice = TempList.Where(y => y.RefDate == c).Sum(x => x.HalfRefPrice),
                    HalfRefFee = TempList.Where(y => y.RefDate == c).Sum(x => x.HalfRefFee)
                };
                ResultList.Add(ccc);
            }
            MonLuggrefClass zzz = new MonLuggrefClass
            {
                RefDate = "合计",
                FullRefCount = 0,
                FullRefPrice = 0,
                HalfRefCount = 0,
                HalfRefPrice = 0,
                HalfRefFee = 0
            };

            foreach (var z in ResultList)
            {
                zzz.FullRefCount += z.FullRefCount;
                zzz.FullRefPrice += z.FullRefPrice;
                zzz.HalfRefCount += z.HalfRefCount;
                zzz.HalfRefPrice += z.HalfRefPrice;
                zzz.HalfRefFee += z.HalfRefFee;
            }
            ResultList.Add(zzz);

            return ResultList;
        }

        public List<MonRollrefClass> GetMonRollref(short Amx, string DayFrom, string DayTo)
        {
            var Result = from r in DC.Rollout
                         join a in DC.Agent on r.Amx equals a.Amx
                         join p in DC.Vpage on r.Pnx equals p.Pnx
                         join m in DC.Volume on p.Lnx equals m.Lnx
                         join f in DC.Mfare on r.Mnx equals f.Pnx
                         join t in DC.Motype on f.Tmx equals t.Tmx
                         where r.Checker != null && r.Rtime != null
                         && (r.Rtime > DateTime.Parse(DayFrom))
                         && (r.Rtime < DateTime.Parse(DayTo).AddDays(1))
                         && (a.Amx == Amx)
                         select new
                         {
                             SAmx = a.Amx,
                             SAgent = a.Nym,
                             Sclerk = r.Seller,
                             OutDate = r.Stime.ToShortDateString(),
                             Rtime = r.Rtime.GetValueOrDefault(DateTime.Parse("1900-1-1")),
                             Checker = r.Checker == null ? "" : r.Checker,
                             MType = t.Tnym,
                             Rate = f.Rate,
                             Fare = f.Fare,
                             Plus = r.Plus,
                             Plate = r.Plate,
                             Refull = r.Refull.GetValueOrDefault(false),
                             RollFee = f.RollFee
                         };
            List<MonRollrefClass> TempList = new List<MonRollrefClass>();
            List<string> RollDateList = new List<string>();
            foreach (var r in Result)
            {
                MonRollrefClass ttt = new MonRollrefClass
                {
                    RefDate = r.Rtime.ToShortDateString(),
                    FullRefCount = r.Refull ? 1 : 0,
                    FullRefPrice = r.Refull ? doRollout(r.Fare, r.Plus, r.RollFee) : 0,
                    HalfRefCount = !r.Refull ? 1 : 0,
                    HalfRefPrice = !r.Refull ? doRollref(r.Fare, r.Plus, r.RollFee, r.Refull, r.Rate) : 0,
                    HalfRefFee = !r.Refull ? doRollout(r.Fare, r.Plus, r.RollFee) - doRollref(r.Fare, r.Plus, r.RollFee, r.Refull, r.Rate) : 0
                };
                TempList.Add(ttt);
                if (!RollDateList.Contains(ttt.RefDate))
                    RollDateList.Add(ttt.RefDate);
            }

            List<MonRollrefClass> ResultList = new List<MonRollrefClass>();
            foreach (var c in RollDateList)
            {
                MonRollrefClass ccc = new MonRollrefClass
                {
                    RefDate = c,
                    FullRefCount = TempList.Where(y => y.RefDate == c).Sum(x => x.FullRefCount),
                    FullRefPrice = TempList.Where(y => y.RefDate == c).Sum(x => x.FullRefPrice),
                    HalfRefCount = TempList.Where(y => y.RefDate == c).Sum(x => x.HalfRefCount),
                    HalfRefPrice = TempList.Where(y => y.RefDate == c).Sum(x => x.HalfRefPrice),
                    HalfRefFee = TempList.Where(y => y.RefDate == c).Sum(x => x.HalfRefFee)
                };
                ResultList.Add(ccc);
            }
            MonRollrefClass zzz = new MonRollrefClass
            {
                RefDate = "合计",
                FullRefCount = 0,
                FullRefPrice = 0,
                HalfRefCount = 0,
                HalfRefPrice = 0,
                HalfRefFee = 0
            };

            foreach (var z in ResultList)
            {
                zzz.FullRefCount += z.FullRefCount;
                zzz.FullRefPrice += z.FullRefPrice;
                zzz.HalfRefCount += z.HalfRefCount;
                zzz.HalfRefPrice += z.HalfRefPrice;
                zzz.HalfRefFee += z.HalfRefFee;
            }
            ResultList.Add(zzz);

            return ResultList;
        }
    }

}
